using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using ctsu.BaseForms;
using JRO;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ctsu.Data;

namespace ctsu.Admin.BusLogic
{

	public class ArpUtils
	{
		public static string _arpDB = "c:\\Arp\\Data2008\\ARP2008.MDB";
		public static string _arpCompactDB = "c:\\Arp\\Data2008\\ARP2008Comp.MDB";
		public static string _arpConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + _arpDB;
		public static string _arpCompactDest = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" +
			_arpCompactDB + ";Jet OLEDB:Engine Type=4";

		public ArpUtils()
		{
		}

		#region access_db utils
		public static bool ReplaceDBWithCompactDB()
		{
			StringBuilder sb = new StringBuilder();
			try
			{
				if (File.Exists(_arpCompactDB))
				{
					File.Copy(_arpDB, _arpDB + "Copy", true);
					File.Copy(_arpCompactDB, _arpDB, true);
					File.Delete(_arpCompactDB);
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in replacing database with compact database.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
		}

		public static bool CompactDB()
		{
			StringBuilder sb = new StringBuilder();
			JRO.JetEngine je = new JetEngine();
			try
			{
				je.CompactDatabase(_arpConnStr, _arpCompactDest);
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in compacting database.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
		}

		public static bool TestArpConnection()
		{
			OleDbConnection conn = null;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}
		#endregion access_db utils

		#region arp(access) table utils
		public static DataTable GetArpDataTable(string table)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT * FROM " + table;

			OleDbConnection conn = null;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

				DataTable dt = new DataTable();
				da.Fill(dt);
				return dt;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving active protocols");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool AddSpjInvToArp(int protoArpID, int invArpID, int invType)
		{
			OleDbConnection conn = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				string sql = "INSERT INTO SPJINV(INV_PROT,INV_NO,INV_TYPE) " +
					" VALUES(?,?,?)";
				OleDbCommand cmd = new OleDbCommand(sql, conn);

				OleDbParameter param = new OleDbParameter("INV_PROT", OleDbType.Integer, 0, "INV_PROT");
				param.Value = protoArpID;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("INV_NO", OleDbType.Integer, 0, "INV_NO");
				param.Value = invArpID;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("INV_TYPE", OleDbType.Integer, 0, "INV_TYPE");
				param.Value = invType;
				cmd.Parameters.Add(param);

				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}
		#endregion arp(access) table utils

		public static bool InitializeProtocolsCensus()
		{
			string sql = "UPDATE SPJPRO SET INPAT_A=0,INPAT_B=0,INPAT_D=0, " +
				" SCAT_A=0,SCAT_B=0,SCAT_D=0, " +
				" OUTPAT_A=0,OUTPAT_B=0,OUTPAT_D=0, " +
				" SCVS_A=0,SCVS_B=0,SCVS_D=0,A_ORVS=0,B_ORVS=0";

			OleDbConnection conn = null;
			OleDbCommand cmd = null;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql, conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdateArpProtocolsScatterNurseHours(int arpID, int cat, int tothours)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("UPDATE SPJPRO SET ");

			string field = "";
			switch (cat)
			{
				case 2: //"A"					
					field = "SCVS_A";
					break;
				case 3: //"B"
					field = "SCVS_B";
					break;
				case 5: //"D"
					field = "SCVS_D";
					break;
			}
			if (field.Length == 0)
				return false;
			sql.Append(field + "=" + tothours);
			sql.Append(" WHERE UNIQUEFIELD=" + arpID);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdateArpProtocolsOutpatDays(int arpID, int cat, int totvisits)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("UPDATE SPJPRO SET ");

			string field = "";
			switch (cat)
			{
				case 2: //"A"					
					field = "OUTPAT_A";
					break;
				case 3: //"B"
					field = "OUTPAT_B";
					break;
				case 5: //"D"
					field = "OUTPAT_D";
					break;
			}
			if (field.Length == 0)
				return false;
			sql.Append(field + "=" + totvisits);
			sql.Append(" WHERE UNIQUEFIELD=" + arpID);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdateArpProtocolsInpatDays(int arpID, int cat, int bed, int totdays)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("UPDATE SPJPRO SET ");

			string field = "";
			switch (cat)
			{
				case 2: //"A"
					if (bed == 2)
						field = "INPAT_A";
					else
						field = "SCAT_A";
					break;
				case 3: //"B"
					if (bed == 2)
						field = "INPAT_B";
					else
						field = "SCAT_B";
					break;
				case 5: //"D"
					if (bed == 2)
						field = "INPAT_D";
					else
						field = "SCAT_D";
					break;
			}
			if (field.Length == 0)
				return false;
			sql.Append(field + "=" + totdays);
			sql.Append(" WHERE UNIQUEFIELD=" + arpID);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool SetProtocolOutpatientCategoryDaysCensus(DataTable dt, DataTable dt2)
		{
			string file = CremaConstants.ReportsDirecotry + "OutpatientCategoryDaysCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			int arpID;
			int cat;
			int totvisits;

			foreach (DataRow dr in dt.Rows)
			{
				arpID = (int)dr["ArpID"];
				cat = (int)dr["PCategory"];
				totvisits = (int)dr["totvisits"];
				//look for any excess hours for the same protocol,category
				DataRow dr2 = dt2.Rows.Find(new object[] { arpID, cat });
				if (dr2 != null)
					totvisits += (int)dr2["totvisits"];
				try
				{
					if (!UpdateArpProtocolsOutpatDays(arpID, cat, totvisits))
						sw.WriteLine("arpID: " + arpID + " - ERROR - update failed.");

				}
				catch (Exception ex)
				{
					sw.WriteLine("arpID: " + arpID + " - EXCEPTION - " + ex.Message);
					continue;
				}
			}
			sw.Close();
			return true;
		}

		public static bool SetProtocolOutpatientCategoryDaysCensus(DataTable dt)
		{
			string file = CremaConstants.ReportsDirecotry + "OutpatientCategoryDaysCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			int arpID;
			int prevArpID;
			int cat;
			int prevCat;
			int totvisits;
			int prevTotvisits;
			DataRow dr;

			//start outside the loop
			dr = dt.Rows[0];
			arpID = (int)dr["ArpID"];
			cat = (int)dr["PCategory"];
			totvisits = (int)dr["totvisits"];
			prevArpID = arpID;
			prevCat = cat;
			prevTotvisits = totvisits;

			//start the loop on the second row
			for (int i = 1; i < dt.Rows.Count; i++)
			{
				dr = dt.Rows[i];
				arpID = (int)dr["ArpID"];
				if (arpID == 102)
					Console.WriteLine("");
				cat = (int)dr["PCategory"];
				totvisits = (int)dr["totvisits"];
				if (arpID == prevArpID && cat == prevCat)
				{
					prevTotvisits += totvisits;
				}
				else
				{
					try
					{
						if (!UpdateArpProtocolsOutpatDays(prevArpID, prevCat, prevTotvisits))
							sw.WriteLine("arpID: " + arpID + " - ERROR - update failed.");

					}
					catch (Exception ex)
					{
						sw.WriteLine("arpID: " + arpID + " - EXCEPTION - " + ex.Message);
						continue;
					}
					prevTotvisits = totvisits;
				}
				prevArpID = arpID;
				prevCat = cat;
			}
			//update the last one
			try
			{
				if (!UpdateArpProtocolsOutpatDays(prevArpID, prevCat, prevTotvisits))
					sw.WriteLine("arpID: " + arpID + " - ERROR - update failed.");

			}
			catch (Exception ex)
			{
				sw.WriteLine("arpID: " + arpID + " - EXCEPTION - " + ex.Message);
			}
			finally
			{
				sw.Close();
			}
			return true;
		}

		public static int MyRound(decimal num)
		{
			string sNum = "";
			int decidingNum = 0;
			int pos = 0;
			int intPart = 0;

			sNum = num.ToString();
			pos = sNum.IndexOf(".");
			if (pos == -1) //no decimal
				return Convert.ToInt32(num);

			intPart = Convert.ToInt32(sNum.Substring(0, pos));
			decidingNum = Convert.ToInt32(sNum.Substring(pos + 1, 1));
			if (decidingNum > 4)
				intPart++;

			return intPart;
		}

		public static bool SetProtocolScatterNurseCategoryHoursCensus(DataTable dt)
		{
			string file = CremaConstants.ReportsDirecotry + "ScatterNurseCategoryHoursCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			int arpID;
			int cat;
			decimal tothours;
			int rndHours;

			foreach (DataRow dr in dt.Rows)
			{
				arpID = (int)dr["ArpID"];
				cat = (int)dr["PCategory"];
				tothours = Convert.ToDecimal(dr["tothours"]);
				rndHours = MyRound(tothours);
				if (rndHours == 0)
					rndHours = 1;
				try
				{
					if (!UpdateArpProtocolsScatterNurseHours(arpID, cat, rndHours))
						sw.WriteLine("arpID: " + arpID + " - ERROR - update failed.");

				}
				catch (Exception ex)
				{
					sw.WriteLine("arpID: " + arpID + " - EXCEPTION - " + ex.Message);
					continue;
				}
			}
			sw.Close();
			return true;
		}

		public static bool SetOutpatientAgeVisitsCategoryCensus()
		{
			DataTable dt1;
			DataTable dt2;

			string criteria = "";
			string criteria2 = "";
			//for each age criteria
			for (int i = 0; i < 5; i++)
			{
				switch (i)
				{
					case 0:
						criteria = " AND Age < 1 ";
						criteria2 = "a. less than 1 year";
						break;
					case 1:
						criteria = " AND (Age >= 1 AND Age < 13) ";
						criteria2 = "b. 1 thru 12 years";
						break;
					case 2:
						criteria = " AND (Age > =13 AND Age < 22) ";
						criteria2 = "c. 13 thru 21 years";
						break;
					case 3:
						criteria = " AND (Age > =22 AND Age < 66) ";
						criteria2 = "d. 22 thru 65 years";
						break;
					case 4:
						criteria = " AND Age >= 66 ";
						criteria2 = "e. 66 and over";
						break;
				}

				int totVisits = 0;
				//for each category
				for (int cat = 2; cat < 6; cat++)
				{
					//cat = 4 "C"
					if (cat == 4)
						continue;
					dt1 = ArpUtils.GetOutpatientVisitsAgeCategoryCensus(criteria, cat);
					dt2 = ArpUtils.GetAdmissionVisitsAgeCategoryExcessHoursCensus(criteria, cat);

					totVisits = 0;
					if (dt1 != null)
						if (dt1.Rows.Count > 0)
							totVisits = (int)dt1.Rows[0]["totvisits"];
					if (dt2 != null)
						if (dt2.Rows.Count > 0)
							totVisits += (int)dt2.Rows[0]["totvisits"];

					UpdateCn_OpAge(cat, criteria2, totVisits);
				}
			}
			return true;
		}

		public static bool SetOutpatientHoursVisitsCategoryCensus()
		{
			DataTable dt1;
			DataTable dt2;

			string criteria = "";
			string criteria2 = "";
			//for each age criteria
			for (int i = 0; i < 5; i++)
			{
				switch (i)
				{
					case 0:
						criteria = " AND Hours > 0 AND Hours < 1 ";
						criteria2 = "a.  < 1 hour";
						break;
					case 1:
						criteria = " AND (Hours >= 1 AND Hours <= 3) ";
						criteria2 = "b.  >= 1 and <= 3";
						break;
					case 2:
						criteria = " AND (Hours > 3 AND Hours <= 6) ";
						criteria2 = "c.  >  3 and <= 6";
						break;
					case 3:
						criteria = " AND (Hours > 6 AND Hours <= 10) ";
						criteria2 = "d.  >  6 and <= 10";
						break;
					case 4:
						criteria = " AND Hours > 10 ";
						criteria2 = "e.  > 10 hours";
						break;
				}

				int totVisits = 0;
				//for each category
				for (int cat = 2; cat < 6; cat++)
				{
					//cat = 4 "C"
					if (cat == 4)
						continue;
					dt1 = ArpUtils.GetOutpatientVisitsHoursCategoryCensus(criteria, cat);
					dt2 = ArpUtils.GetAdmissionVisitsHoursCategoryExcessHoursCensus(criteria, cat);

					totVisits = 0;
					if (dt1 != null)
						if (dt1.Rows.Count > 0)
							totVisits = (int)dt1.Rows[0]["totvisits"];
					if (dt2 != null)
						if (dt2.Rows.Count > 0)
							totVisits += (int)dt2.Rows[0]["totvisits"];

					UpdateCn_Op(cat, criteria2, totVisits);
				}
			}
			return true;
		}

		public static bool UpdateCensusPS(Hashtable ht)
		{
			string key = "";
			string year = "";
			string month = "";
			int pos = 0;

			string where = "";
			string sql = "UPDATE CEN_PS SET A_DAYS=?,B_DAYS=?,C_DAYS=?,D_DAYS=?," +  //SET DAYS_OPEN=?,
				"SA_DAYS=?,SB_DAYS=?,SD_DAYS=?,A_VISITS=?,B_VISITS=?,C_VISITS=?,D_VISITS=? ";
			string sqlComp = "";

			OleDbConnection conn = new OleDbConnection(_arpConnStr);
			conn.Open();
			OleDbCommand cmd = new OleDbCommand(sqlComp, conn);

			OleDbParameter param = new OleDbParameter("A_DAYS", OleDbType.Integer, 0, "A_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("B_DAYS", OleDbType.Integer, 0, "B_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("C_DAYS", OleDbType.Integer, 0, "C_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("D_DAYS", OleDbType.Integer, 0, "D_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("SA_DAYS", OleDbType.Integer, 0, "SA_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("SB_DAYS", OleDbType.Integer, 0, "SB_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("SD_DAYS", OleDbType.Integer, 0, "SD_DAYS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("A_VISITS", OleDbType.Integer, 0, "A_VISITS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("B_VISITS", OleDbType.Integer, 0, "B_VISITS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("C_VISITS", OleDbType.Integer, 0, "C_VISITS");
			cmd.Parameters.Add(param);

			param = new OleDbParameter("D_VISITS", OleDbType.Integer, 0, "D_VISITS");
			cmd.Parameters.Add(param);

			try
			{
				CenPSCounter cc = null;
				foreach (object obj in ht.Keys)
				{
					cc = (CenPSCounter)ht[obj];
					key = obj.ToString();
					pos = key.IndexOf("/");
					//key has form of yyyy/mm
					year = key.Substring(0, pos);
					month = key.Substring(pos + 1);
					year = year.Substring(3);

					where = " WHERE YR=" + year + " AND MON=" + month;
					sqlComp = sql + where;

					cmd.CommandText = sqlComp;

					//cmd.Parameters["DAYS_OPEN"].Value = cc.DaysOpen.Count;
					cmd.Parameters["A_DAYS"].Value = cc.ADays;
					cmd.Parameters["B_DAYS"].Value = cc.BDays;
					cmd.Parameters["C_DAYS"].Value = cc.CDays;
					cmd.Parameters["D_DAYS"].Value = cc.DDays;
					cmd.Parameters["SA_DAYS"].Value = cc.SADays;
					cmd.Parameters["SB_DAYS"].Value = cc.SBDays;
					cmd.Parameters["SD_DAYS"].Value = cc.SDDays;
					cmd.Parameters["A_VISITS"].Value = cc.AVisits;
					cmd.Parameters["B_VISITS"].Value = cc.BVisits;
					cmd.Parameters["C_VISITS"].Value = cc.CVisits;
					cmd.Parameters["D_VISITS"].Value = cc.DVisits;

					cmd.ExecuteNonQuery();
				}
				return true;
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				return false;
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}

		}

		public static bool SetCensusPS(Hashtable ht, DateTime beginDate, DateTime endDate)
		{
			string file = CremaConstants.ReportsDirecotry + "CensusPSExclusions";
			StreamWriter sw = new StreamWriter(file, false);

			string sql = "SELECT * FROM aaCombineCensus";
			//SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			int ord = 0;
			int visitID = 0;
			int visitType = 0;
			int cat = 0;
			int bed = 0;
			int days = 0;
			decimal hours = 0;
			//int iHours = 0;
			DateTime start;
			string key = "";
			CenPSCounter cenCounter;

			try
			{
				//conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					ord = reader.GetOrdinal("VisitType");
					visitType = reader.GetInt32(ord);

					ord = reader.GetOrdinal("VisitID");
					visitID = reader.GetInt32(ord);
					if (visitID == 2708)
					{
						//bool b = true;
					}

					ord = reader.GetOrdinal("PCategory");
					cat = reader.GetInt32(ord);

					ord = reader.GetOrdinal("VisitDate");
					start = reader.GetDateTime(ord);

					bool doOnceOnly;
					switch (visitType)
					{
						case (1): //admission
							doOnceOnly = false;

							if (start.CompareTo(beginDate) < 0)
								start = beginDate;
							else
							{
								/*don't add a day if scatter b, scatter b is calculated 
								 * only if they had an ancillary for that day
								 */
								if (cat != 3)
									start = start.AddDays(1);
							}

							ord = reader.GetOrdinal("BedType");
							bed = reader.GetInt32(ord);

							ord = reader.GetOrdinal("Days");
							days = reader.GetInt32(ord);

							ord = reader.GetOrdinal("Hours");
							hours = reader.GetDecimal(ord);

							for (int i = 0; i < days; i++)
							{
								key = start.Year.ToString() + "/" + start.Month.ToString();
								if (!ht.ContainsKey(key))
								{
									sw.WriteLine("ID: " + visitID + " - key: " + key.ToString() + " not found.");
									continue;
								}
								cenCounter = (CenPSCounter)ht[key];

								//days open
								if (!cenCounter.DaysOpen.Contains(start.Day))
									cenCounter.DaysOpen.Add(start.Day, 1);

								if (cat == 2) //A
								{
									if (bed == 2)
										cenCounter.ADays++;
									else
										cenCounter.SADays++;
								}
								if (cat == 3) //B
								{
									if (bed == 2)
										cenCounter.BDays++;
									else
										cenCounter.SBDays++;
								}
								if (cat == 4) //C
								{
									if (bed == 2)
										cenCounter.CDays++;
								}
								if (cat == 5) //D
								{
									if (bed == 2)
										cenCounter.DDays++;
									else
										cenCounter.SDDays++;
								}
								//iHours = ArpUtils.MyRound(hours);
								if (hours > 0 && bed == 2 && (!doOnceOnly))
								{
									doOnceOnly = true;
									if (cat == 2) //A										
										cenCounter.AVisits++;
									if (cat == 3) //B
										cenCounter.BVisits++;
									if (cat == 4) //C
										cenCounter.CVisits++;
									if (cat == 5) //D									
										cenCounter.DVisits++;
								}
								start = start.AddDays(1);
							}

							break;
						case (2): //outpatient
							key = start.Year.ToString() + "/" + start.Month.ToString();
							if (!ht.ContainsKey(key))
							{
								sw.WriteLine("ID: " + visitID + " - key: " + key.ToString() + " not found.");
								continue;
							}
							cenCounter = (CenPSCounter)ht[key];

							//days open
							if (!cenCounter.DaysOpen.Contains(start.Day))
								cenCounter.DaysOpen.Add(start.Day, 1);

							if (cat == 2) //A										
								cenCounter.AVisits++;
							if (cat == 3) //B
								cenCounter.BVisits++;
							if (cat == 4) //C
								cenCounter.CVisits++;
							if (cat == 5) //D									
								cenCounter.DVisits++;
							break;
						case (3): //scatter nurse hours
							break;
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();
				sw.Close();
			}

		}

		public static Hashtable SetDaysOpenHash(DateTime beginDate, DateTime endDate)
		{
			Hashtable ht = new Hashtable();
			int month = beginDate.Month;
			int year = beginDate.Year;
			int eMonth = endDate.Month;
			int eYear = endDate.Year;
			//int days = 0;

			string key = "";
			while (true)
			{
				key = year.ToString() + "/" + month.ToString();
				//days = DateTime.DaysInMonth(year,month);			
				CenPSCounter cc = new CenPSCounter();
				ht.Add(key, cc);

				if (month == 12)
				{
					month = 1;
					year++;
				}
				else
					month++;

				if (year < eYear)
					continue;
				else
					if (month > eMonth)
						break;
			}
			return ht;
		}

		public static bool SetProtocolAdmissionAgeVisitsDaysCensus()
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder sbSql = new StringBuilder();

			string sql = "SELECT PCategory, BedType, COUNT(VisitID) AS visits, Sum(Days) as days " +
				" FROM aaCombineCensus WHERE VisitType=1 ";

			//SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			int cat;
			int bed;
			int visits;
			int days;
			try
			{
				//conn.Open();
				string criteria = "";
				for (int i = 0; i < 5; i++)
				{
					sbSql = new StringBuilder(sql);
					switch (i)
					{
						case 0:
							criteria = " AND Age < 1 ";
							break;
						case 1:
							criteria = " AND (Age >= 1 AND Age < 13) ";
							break;
						case 2:
							criteria = " AND (Age > =13 AND Age < 22) ";
							break;
						case 3:
							criteria = " AND (Age > =22 AND Age < 66) ";
							break;
						case 4:
							criteria = " AND Age >= 66 ";
							break;
					}
					sbSql.Append(criteria + " GROUP BY PCategory,BedType");

					try
					{
						reader = DataAccess.GetDataReader(sbSql.ToString());
						while (reader.Read())
						{
							cat = reader.GetInt32(0);
							bed = reader.GetInt32(1);
							visits = reader.GetInt32(2);
							days = reader.GetInt32(3);

							if (bed == 2)
								UpdateArpCEN_IPOr_SB("CEN_IP", i, cat, visits, days);
							else
								UpdateArpCEN_IPOr_SB("CEN_SB", i, cat, visits, days);
						}
					}
					catch (Exception exi)
					{
						Console.WriteLine(exi.Message);
					}
					finally
					{
						if (reader != null)
							reader.Close();
					}

				}
				return true;
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				throw ex;
			}
			finally
			{
				//conn.Close();
			}
		}

		public static bool SetProtocolOutpatientAgeVisitsCensus()
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder sbSql = new StringBuilder();

			string sql = "SELECT PCategory, BedType, COUNT(VisitID) AS visits, Sum(Days) as days " +
				" FROM aaCombineCensus WHERE VisitType=1 ";

			//SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			int cat;
			int bed;
			int visits;
			int days;
			try
			{
				//conn.Open();
				string criteria = "";
				for (int i = 0; i < 5; i++)
				{
					sbSql = new StringBuilder(sql);
					switch (i)
					{
						case 0:
							criteria = " AND Age < 1 ";
							break;
						case 1:
							criteria = " AND (Age >= 1 AND Age < 13) ";
							break;
						case 2:
							criteria = " AND (Age > =13 AND Age < 22) ";
							break;
						case 3:
							criteria = " AND (Age > =22 AND Age < 66) ";
							break;
						case 4:
							criteria = " AND Age >= 66 ";
							break;
					}
					sbSql.Append(criteria + " GROUP BY PCategory,BedType");

					try
					{
						reader = DataAccess.GetDataReader(sbSql.ToString());
						while (reader.Read())
						{
							cat = reader.GetInt32(0);
							bed = reader.GetInt32(1);
							visits = reader.GetInt32(2);
							days = reader.GetInt32(3);

							if (bed == 2)
								UpdateArpCEN_IPOr_SB("CEN_IP", i, cat, visits, days);
							else
								UpdateArpCEN_IPOr_SB("CEN_SB", i, cat, visits, days);
						}
					}
					catch (Exception exi)
					{
						Console.WriteLine(exi.Message);
					}
					finally
					{
						if (reader != null)
							reader.Close();
					}

				}
				return true;
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				throw ex;
			}
			finally
			{
				//conn.Close();
			}
		}

		public static bool InitializeArpCEN_PS()
		{
			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			string sql = "UPDATE CEN_PS SET DAYS_OPEN=0,A_DAYS=0,B_DAYS=0,C_DAYS=0,D_DAYS=0, " +
				" SA_DAYS=0,SB_DAYS=0,SD_DAYS=0,A_VISITS=0,B_VISITS=0,C_VISITS=0,D_VISITS=0, " +
				" A_ORVS=0,B_ORVS=0";

			try
			{
				string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" +
					"c:\\Arp\\Data2005\\ARP2005.MDB";
				//conn = new OleDbConnection(_arpConnStr);
				//temp string for testing
				conn = new OleDbConnection(connStr);
				conn.Open();

				cmd = new OleDbCommand(sql, conn);
				cmd.ExecuteNonQuery();

				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool InitializeArpCEN_OPAGE()
		{
			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			string sql = "UPDATE CEN_OPAGE SET A_VISITS=0,B_VISITS=0,D_VISITS=0 ";

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				cmd = new OleDbCommand(sql, conn);
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool InitializeArpPCCARTables()
		{
			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			string sql = "UPDATE PCCAR1 ";
			string sql2 = "UPDATE PCCAR2 ";
			string fields = "SET PD_ADAYS=0,PD_COST=0,SB_ADAYS=0,SB_COST=0," +
				"CR_BDAYS=0,CR_BCOST=0,CR_CDAYS=0,CR_CCOST=0,CR_DDAYS=0,CR_DCOST=0," +
				"ANC_ADAYS=0,ANC_ACOST=0,ANC_BDAYS=0,ANC_BCOST=0,ANC_SBADAY=0,ANC_SBACST=0," +
				"ANC_SBBDAY=0,ANC_SBBCST=0,SP_CHG=0,ANC_AVISIT=0,ANC_AOCOST=0,ANC_BVISIT=0," +
				"ANC_BOCOST=0,ANC_ONLY_CHG=0";

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				cmd = new OleDbCommand(sql + fields, conn);
				cmd.ExecuteNonQuery();

				cmd.CommandText = sql2 + fields;
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}

		}

		public static bool InitializeArpCEN_OP()
		{
			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			string sql = "UPDATE CEN_OP SET A_VISITS=0,B_VISITS=0,C_VISITS=0,D_VISITS=0 ";

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				cmd = new OleDbCommand(sql, conn);
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool InitializeArpCEN_IPOr_SB(string table)
		{
			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			string sql = "UPDATE " + table + " SET A_ADM=0,B_ADM=0,D_ADM=0," +
				"A_DAYS=0,B_DAYS=0,D_DAYS=0 ";

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				cmd = new OleDbCommand(sql, conn);
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdateCn_Op(int cat, string criteria, int visits)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("UPDATE CEN_OP SET ");

			string field = "";
			switch (cat)
			{
				case 2: //"A"
					field = "A_VISITS=";
					break;
				case 3: //"B"
					field = "B_VISITS=";
					break;
				case 5: //"D"
					field = "D_VISITS=";
					break;
			}
			field += visits;

			sql.Append(field + " WHERE LEN_STAY ='" + criteria + "'");

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdateCn_OpAge(int cat, string criteria, int visits)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("UPDATE CEN_OPAGE SET ");

			string field = "";
			switch (cat)
			{
				case 2: //"A"
					field = "A_VISITS=";
					break;
				case 3: //"B"
					field = "B_VISITS=";
					break;
				case 5: //"D"
					field = "D_VISITS=";
					break;
			}
			field += visits;

			sql.Append(field + " WHERE AGE ='" + criteria + "'");


			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdatePCCARAdmissionDays(int table, int ancAdays, int ancBdays, int sbAdays, int sbBdays, int crCdays, int crDdays)
		{
			StringBuilder sql = new StringBuilder();

			if (table == 1)
				sql.Append("UPDATE PCCAR1 ");
			else
				sql.Append("UPDATE PCCAR2 ");

			sql.Append("SET PD_ADAYS=" + ancAdays + ",ANC_ADAYS=" + ancAdays +
				",CR_BDAYS=" + ancBdays + ",ANC_BDAYS=" + ancBdays +
				",CR_CDAYS=" + crCdays + ",CR_DDAYS=" + crDdays +
				",SB_ADAYS=" + sbAdays + ",ANC_SBADAY=" + sbAdays +
				",ANC_SBBDAY=" + sbBdays);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdatePCCAROutpatientDays(int table, int ancAvisit, int ancBvisit, int ocrCdays, int ocrDdays)
		{
			StringBuilder sql = new StringBuilder();

			if (table == 1)
				sql.Append("UPDATE PCCAR1 ");
			else
				sql.Append("UPDATE PCCAR2 ");

			sql.Append("SET ANC_AVISIT=" + ancAvisit + ",ANC_BVISIT=" + ancBvisit +
				",OCR_BDAYS=" + ancBvisit + ",OCR_CDAYS=" + ocrCdays +
				",OCR_DDAYS=" + ocrDdays);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool UpdatePCCARAncillaryAdmission(int table, decimal ancAcost, decimal ancBcost, decimal ancSBAcost, decimal ancSBBcost)
		{
			StringBuilder sql = new StringBuilder();

			if (table == 1)
				sql.Append("UPDATE PCCAR1 ");
			else
				sql.Append("UPDATE PCCAR2 ");

			sql.Append("SET ANC_ACOST=" + ancAcost + ",ANC_BCOST=" + ancBcost +
				",ANC_SBACST=" + ancSBAcost + ",ANC_SBBCST=" + ancSBBcost);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}

		}

		public static bool UpdatePCCARAncillaryOutpatient(int table, decimal ancAOcost, decimal ancBOcost)
		{
			StringBuilder sql = new StringBuilder();

			if (table == 1)
				sql.Append("UPDATE PCCAR1 ");
			else
				sql.Append("UPDATE PCCAR2 ");

			sql.Append("SET ANC_AOCOST=" + ancAOcost + ",ANC_BOCOST=" + ancBOcost);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}

		}

		public static bool UpdateArpCEN_IPOr_SB(string table, int i, int cat, int visits, int days)
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("UPDATE " + table + " SET ");

			string field1 = "";
			string field2 = "";
			string where = "";
			switch (cat)
			{
				case 2: //"A"
					field1 = "A_ADM=";
					field2 = "A_DAYS=";
					break;
				case 3: //"B"
					field1 = "B_ADM=";
					field2 = "B_DAYS=";
					break;
				case 5: //"D"
					field1 = "D_ADM=";
					field2 = "D_DAYS=";
					break;
			}
			field1 += visits;
			field2 += days;

			sql.Append(field1 + "," + field2);

			switch (i)
			{
				case 0:
					where = " WHERE AGE = 'a. less than 1 year'";
					break;
				case 1:
					where = " WHERE AGE = 'b. 1 thru 12 years'";
					break;
				case 2:
					where = " WHERE AGE = 'c. 13 thru 21 years'";
					break;
				case 3:
					where = " WHERE AGE = 'd. 22 thru 65 years'";
					break;
				case 4:
					where = " WHERE AGE = 'e. 66 and over'";
					break;
			}
			sql.Append(where);

			OleDbConnection conn = null;
			OleDbCommand cmd = null;

			try
			{
				conn = new OleDbConnection(_arpConnStr);
				cmd = new OleDbCommand(sql.ToString(), conn);
				conn.Open();
				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static bool SetProtocolAdmissionCategoryDaysCensus(DataTable dt)
		{
			string file = CremaConstants.ReportsDirecotry + "AdmissionCategoryDaysCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			int arpID;
			int cat;
			int bed;
			int totdays;


			foreach (DataRow dr in dt.Rows)
			{
				arpID = (int)dr["ArpID"];
				cat = (int)dr["PCategory"];
				bed = (int)dr["BedType"];
				totdays = (int)dr["totdays"];
				try
				{
					if (!UpdateArpProtocolsInpatDays(arpID, cat, bed, totdays))
						sw.WriteLine("arpID: " + arpID + " - ERROR - update failed.");

				}
				catch (Exception ex)
				{
					sw.WriteLine("arpID: " + arpID + " - EXCEPTION - " + ex.Message);
					continue;
				}
			}
			sw.Close();
			return true;
		}

		public static DataTable GetProtocolAdmissionCategoryExcessHoursCensus()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT ap.ArpID,a.PCategory,COUNT(a.VisitID) as totvisits " +
				" FROM aaCombineCensus a, ArpActiveProtocols ap " +
				" WHERE VisitType=1 AND a.Hours>0 AND a.protocol=ap.ProtocolID" +
				" GROUP BY ap.ArpID, a.PCategory " +
				" ORDER BY ap.ArpID";

			//SqlConnection conn = DataAccess.GetSqlConnection();			
			DataSet ds;
			try
			{
				//conn.Open();				
				ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in calculating protocol outpatient category days.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static DataTable GetProtocolScatterNurseCategoryHoursCensus()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT ap.ArpID,a.PCategory,SUM(a.Hours) as tothours  " +
				" FROM aaCombineCensus a, ArpActiveProtocols ap " +
				" WHERE VisitType=3 AND a.protocol=ap.ProtocolID" +
				" GROUP BY ap.ArpID, a.PCategory " +
				" ORDER BY ap.ArpID";

			SqlConnection conn = DataAccess.GetSqlConnection();
			DataSet ds;
			try
			{
				conn.Open();
				ds = ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in calculating protocol scatter nurse category hours.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetOutpatientVisitsHoursCategoryCensus(string hourCriteria, int category)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT PCategory,COUNT(VisitID) as totvisits " +
				" FROM aaCombineCensus " +
				" WHERE VisitType=2 " + hourCriteria +
				" AND PCategory=" + category +
				" GROUP BY PCategory";

			SqlConnection conn = DataAccess.GetSqlConnection();
			DataSet ds;
			try
			{
				conn.Open();
				ds = ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in getting cen_op.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetOutpatientVisitsAgeCategoryCensus(string ageCriteria, int category)
		{
			StringBuilder sb = new StringBuilder();
			//misc patient account is not included in aaCombineCensus
			string sql = "SELECT PCategory,COUNT(VisitID) as totvisits " +
				" FROM aaCombineCensus " +
				" WHERE VisitType=2 " + ageCriteria +
				" AND PCategory=" + category +
				" GROUP BY PCategory";

			//SqlConnection conn = DataAccess.GetSqlConnection();			
			DataSet ds;
			try
			{
				//conn.Open();				
				ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in calculating protocol outpatient category days.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static DataTable GetAdmissionVisitsAgeCategoryExcessHoursCensus(string ageCriteria, int category)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT PCategory,COUNT(VisitID) as totvisits " +
				" FROM aaCombineCensus " +
				" WHERE VisitType=1 AND Hours>0 " + ageCriteria +
				" AND PCategory=" + category +
				" AND BedType=2 " +
				" GROUP BY PCategory";


			//SqlConnection conn = DataAccess.GetSqlConnection();			
			DataSet ds;
			try
			{
				//conn.Open();				
				ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in calculating protocol outpatient category days.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static DataTable GetAdmissionVisitsHoursCategoryExcessHoursCensus(string hourCriteria, int category)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT PCategory,COUNT(VisitID) as totvisits " +
				" FROM aaCombineCensus " +
				" WHERE VisitType=1 " + hourCriteria +
				" AND PCategory=" + category +
				" AND BedType=2 " +
				" GROUP BY PCategory";

			SqlConnection conn = DataAccess.GetSqlConnection();
			DataSet ds;
			try
			{
				conn.Open();
				ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in getting admission excess hour visits for cen_op.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetProtocolOutpatientCategoryDaysCensus()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT ap.ArpID,a.PCategory,COUNT(a.VisitID) AS totvisits " +
				" FROM aaCombineCensus a JOIN ArpActiveProtocols ap " +
				" ON a.protocol=ap.ProtocolID " +
				" WHERE VisitType=2 " +
				" GROUP BY ap.ArpID, a.PCategory " +
				" UNION " +
				"SELECT ap.ArpID,a.PCategory,COUNT(a.VisitID) as totvisits " +
				" FROM aaCombineCensus a JOIN ArpActiveProtocols ap " +
				" ON a.protocol=ap.ProtocolID " +
				" WHERE VisitType=1 AND a.Hours>0 " +
				" GROUP BY ap.ArpID, a.PCategory " +
				" ORDER BY ap.ArpID";

			SqlConnection conn = DataAccess.GetSqlConnection();
			DataSet ds;
			try
			{
				conn.Open();
				ds = ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in calculating protocol outpatient category days.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetProtocolAdmissionCategoryDaysCensus()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT ap.ArpID,a.PCategory,a.BedType,sum(a.Days) as totdays " +
				" FROM aaCombineCensus a, ArpActiveProtocols ap " +
				" WHERE VisitType=1 AND a.protocol=ap.ProtocolID" +
				" GROUP BY ap.ArpID, a.PCategory, a.BedType ORDER BY ap.ArpID";

			SqlConnection conn = DataAccess.GetSqlConnection();
			DataSet ds;
			try
			{
				conn.Open();
				ds = ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in calculating protocol inpatient category days.");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetInvProtToArp(int protocolID, int protoArpID)
		{
			StringBuilder sb = new StringBuilder();
			//SqlConnection conn = DataAccess.GetSqlConnection();			

			string sql = "SELECT p.StaffTypeID, p.IsContact, i.ArpID " +
				" FROM ProtocolStaff p, ArpActiveInvestigators i " +
				" WHERE p.ProtocolID = " + protocolID +
				" AND p.InvestigatorID = i.InvestigatorID"; ;

			IDataReader reader = null;

			int invType = 0;
			int invArpID = 0;
			bool isContact = false;
			try
			{
				//conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					invType = reader.GetInt32(0);
					isContact = false;
					if (!reader.IsDBNull(1))
						isContact = reader.GetBoolean(1);
					invArpID = reader.GetInt32(2);

					switch (invType)
					{
						case 1:
							break;
						case 2:
							break;
						case 3:
							break;
						case 4:
							break;
					}
					AddSpjInvToArp(protoArpID, invArpID, invType);
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in adding record to SPJINV");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static bool UpdateActiveProtocolWithArpID(int arpID, int protocolID)
		{
			StringBuilder sb = new StringBuilder();
			SqlConnection conn = DataAccess.GetSqlConnection();

			string sql = "UPDATE ArpActiveProtocols SET ArpID=" + arpID +
				" WHERE ProtocolID=" + protocolID;

			try
			{
				int i = DataAccess.ExecuteNonQuery(CommandType.Text, sql);
				if (i < 0)
					return false;
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in updating active protocols with arp id");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool UpdateActiveInvestigatorWithArpID(int arpID, int invID)
		{
			StringBuilder sb = new StringBuilder();
			SqlConnection conn = DataAccess.GetSqlConnection();

			string sql = "UPDATE ArpActiveInvestigators SET ArpID=" + arpID +
				" WHERE InvestigatorID=" + invID;

			try
			{
				int i = DataAccess.ExecuteNonQuery(CommandType.Text, sql);
				if (i < 0)
					return false;
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in updating active investigators with arp id");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static void TruncateTable(string table)
		{
			StringBuilder sb = new StringBuilder();
			SqlConnection conn = DataAccess.GetSqlConnection();

			string sql = "TRUNCATE TABLE " + table;
			SqlCommand cmd = new SqlCommand(sql, conn);

			try
			{
				conn.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				sb.Append("Error truncating table: " + table);
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw (new Exception(sb.ToString()));
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}

		}

		public static void CleanArpTable(string table)
		{
			OleDbConnection conn = null;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				//first clean the table of all rows
				string sql = "DELETE * FROM " + table;
				OleDbCommand cmd = new OleDbCommand(sql, conn);
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static int AddInvestigatorToArp(DataRow dr, string name)
		{
			OleDbConnection conn = null;
			string sVal = "";

			if (name.IndexOf("Weiss") > -1)
				Console.WriteLine("");
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				string sql = "INSERT INTO INVESTIG (LastName,FirstName,Degree,Dept, " +
					" NonHost,CAMPName)" +
					" VALUES(?,?,?,?,?,?)";
				OleDbCommand cmd = new OleDbCommand(sql, conn);

				OleDbParameter param = new OleDbParameter("LastName", OleDbType.VarChar, 20, "LastName");
				param.Value = dr["LastName"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("FirstName", OleDbType.VarChar, 20, "FirstName");
				param.Value = dr["FirstName"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Degree", OleDbType.VarChar, 20, "Degree");
				sVal = dr["Degrees"].ToString();
				if (sVal.Length == 0)
					sVal = " ";
				param.Value = sVal;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Dept", OleDbType.VarChar, 20, "Dept");
				sVal = dr["Dept"].ToString();
				if (sVal.Length == 0)
					sVal = " ";
				param.Value = sVal;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("NonHost", OleDbType.VarChar, 20, "NonHost");
				param.Value = dr["NonHost"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("CAMPName", OleDbType.VarChar, 20, "CAMPName");
				param.Value = name;
				cmd.Parameters.Add(param);
				cmd.ExecuteNonQuery();

				sql = "SELECT MAX(UNIQUEFIELD) FROM INVESTIG";
				cmd = new OleDbCommand(sql, conn);
				int newid = (int)cmd.ExecuteScalar();

				return newid;


			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		public static int AddProtocolToArp(DataRow dr, string protocol, int spid)
		{
			OleDbConnection conn = null;
			string sVal = "";
			int iVal = 0;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				string sql = "INSERT INTO SPJPRO (PROTOCOL,SPID,SHORTTITLE,LONGTITLE,AIDS, " +
					" Summary,Results,StartDate,EndDate,ExpNumPats,MultiCente,Clintrial,Phase)" +
					" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
				OleDbCommand cmd = new OleDbCommand(sql, conn);

				OleDbParameter param = new OleDbParameter("PROTOCOL", OleDbType.VarChar, 12, "PROTOCOL");
				param.Value = protocol;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("SPID", OleDbType.VarChar, 4, "SPID");
				param.Value = spid.ToString("0000");
				cmd.Parameters.Add(param);

				param = new OleDbParameter("SHORTTITLE", OleDbType.VarWChar, 200, "SHORTTITLE");
				param.Value = dr["Title"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("LONGTITLE", OleDbType.VarWChar, 2000, "LONGTITLE");
				param.Value = dr["LongTitle"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("AIDS", OleDbType.Boolean, 0, "AIDS");
				param.Value = dr["Aids"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Summary", OleDbType.VarWChar, 2000, "Summary");
				sVal = dr["ProtoAbstract"].ToString();
				if (sVal.Length == 0)
					sVal = " ";
				param.Value = sVal;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Results", OleDbType.VarWChar, 2000, "Results");
				sVal = dr["Results"].ToString();
				if (sVal.Length == 0)
					sVal = " ";
				param.Value = sVal;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("StartDate", OleDbType.Date, 0, "StartDate");
				param.Value = dr["IrbApproval"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("EndDate", OleDbType.Date, 0, "EndDate");
				param.Value = dr["ProjectedComplete"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("ExpNumPats", OleDbType.Numeric, 0, "ExpNumPats");
				param.Value = dr["ProjectedPatients"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("MultiCente", OleDbType.Boolean, 0, "MultiCente");
				param.Value = dr["MultiCenter"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Clintrial", OleDbType.Boolean, 0, "Clintrial");
				param.Value = dr["ClinicalTrial"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Phase", OleDbType.Numeric, 0, "Phase");
				if (dr["Phase"] == System.DBNull.Value)
					param.Value = 0;
				else
				{
					iVal = Convert.ToInt32(dr["Phase"]);
					if (iVal == -1)
						param.Value = 0;
					else
						param.Value = iVal - 1;
				}
				cmd.Parameters.Add(param);

				cmd.ExecuteNonQuery();

				sql = "SELECT MAX(UNIQUEFIELD) FROM SPJPRO";
				cmd = new OleDbCommand(sql, conn);
				int newid = (int)cmd.ExecuteScalar();

				return newid;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}

		}

		public static DataRow GetProtocolArpInfo(int id)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT Title,LongTitle,ProtoAbstract,Results,IrbApproval,ProjectedComplete, " +
				" ProjectedPatients,Aids,ClinicalTrial,Phase,MultiCenter " +
				" FROM Protocol WHERE ID=" + id;

			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
				{
					if (ds.Tables[0].Rows.Count > 0)
						return ds.Tables[0].Rows[0];
					else
						return null;
				}
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving protocol arp info for id: " + id);
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}

		}

		public static DataRow GetInvestigatorArpInfo(int id)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT i.ID,i.FirstName,i.LastName,p.Display AS Dept,n.Display AS NonHost " +
				" FROM Investigator i, ProtocolDepartment_L p, NonHost_L n " +
				" WHERE i.ID=" + id +
				" AND i.ProtocolDepartment=p.ID " +
				" AND i.NonHost=n.ID";

			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
				{
					DataTable dt = ds.Tables[0];
					if (dt.Rows.Count > 0)
					{
						DataRow dr = dt.Rows[0];
						//add a column for degrees
						DataColumn dc = new DataColumn("Degrees", typeof(System.String));
						dt.Columns.Add(dc);
						dr["Degrees"] = GetInvestigatorDegrees(id);
						return dr;
					}
					else
						return null;
				}
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving investigator arp info for id: " + id);
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}

		}

		private static string GetInvestigatorDegrees(int id)
		{
			StringBuilder sb = new StringBuilder("");
			string sql = "SELECT d.Display " +
				" FROM InvestigatorDegree i, Degree_L d " +
				" WHERE i.InvestigatorID=" + id +
				" AND i.DegreeID=d.ID";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				StringBuilder sd = new StringBuilder("");
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					sd.Append(reader.GetString(0) + ",");
				}

				if (sd.Length > 0)
					//remove last ,
					sd.Remove(sd.Length - 1, 1);
				return sd.ToString();

			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving investigator degrees for id: " + id);
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static void SetActiveInvestigators(DataTable dtActiveProtocols, DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				SqlCommand cmd = new SqlCommand("SetArpActiveInvestigators", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@beginDate", SqlDbType.DateTime));
				cmd.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime));
				cmd.Parameters["@beginDate"].Value = beginDate;
				cmd.Parameters["@endDate"].Value = endDate;
				conn.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				sb.Append("Error in SetActiveInvestigators");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static void SetActiveProtocols(DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			SqlConnection conn = DataAccess.GetSqlConnection();

			SqlCommand cmd = new SqlCommand("SetArpActiveProtocols", conn);
			cmd.CommandType = CommandType.StoredProcedure;

			cmd.Parameters.Add(new SqlParameter("@beginDate", SqlDbType.DateTime));
			cmd.Parameters.Add(new SqlParameter("@endDate", SqlDbType.DateTime));
			cmd.Parameters["@beginDate"].Value = beginDate;
			cmd.Parameters["@endDate"].Value = endDate;

			try
			{
				conn.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting active protocols");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool AddToArpEthnicityCensus(int arpID, int patient, int gender, decimal age, int race, int ethnicity)
		{
			int sex;
			OleDbConnection conn = null;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				string sql = "INSERT INTO SPJETH (ETHPROT,PATIENTID,SEX,AGE,RACE,ETHNICITY)" +
					" VALUES(?,?,?,?,?,?) ";

				OleDbCommand cmd = new OleDbCommand(sql, conn);

				OleDbParameter param = new OleDbParameter("ETHPROT", OleDbType.Integer, 0, "ETHPROT");
				param.Value = arpID;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("PATIENTID", OleDbType.Char, 10, "PATIENTID");
				param.Value = patient.ToString();
				cmd.Parameters.Add(param);

				param = new OleDbParameter("SEX", OleDbType.Integer, 0, "SEX");
				if (gender < 1 || gender > 2)
					sex = 3;
				else
				{
					if (gender == 1)
						sex = 2;
					else
						sex = 1;
				}
				param.Value = sex;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("AGE", OleDbType.Numeric, 0, "AGE");
				param.Value = age;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("RACE", OleDbType.Integer, 0, "RACE");
				param.Value = race;
				cmd.Parameters.Add(param);

				param = new OleDbParameter("ETHNICITY", OleDbType.Integer, 0, "ETHNICITY");
				param.Value = ethnicity;
				cmd.Parameters.Add(param);

				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool CalcActiveEthnicityCensus(DataTable dt)
		{
			string file = CremaConstants.ReportsDirecotry + "ActiveEthnicityCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			string sql = "SELECT Gender,Age,Race,Ethnicity " +
				" FROM aaCombineCensus c ";
			string newSql = "";

			int protocol;
			int patient;
			int arpID;
			int gender;
			decimal age;
			int race;
			int ethnicity;

			//SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			//conn.Open();
			try
			{
				foreach (DataRow dr in dt.Rows)
				{
					arpID = (int)dr["ArpID"];
					protocol = (int)dr["Protocol"];
					patient = (int)dr["Patient"];

					newSql = sql + " WHERE c.Protocol=" + protocol +
						" AND c.Patient=" + patient +
						" ORDER BY c.VisitDate";

					try
					{
						reader = DataAccess.GetDataReader(newSql);
						if (reader.Read())
						{
							gender = reader.GetInt32(0);
							age = reader.GetDecimal(1);
							race = reader.GetInt32(2);
							ethnicity = reader.GetInt32(3);
							if (ethnicity == 255)
								sw.WriteLine("Protocol: " + protocol + ", Patient: " + patient + " ethnicity not entered.");
							AddToArpEthnicityCensus(arpID, patient, gender, age, race, ethnicity);
						}
						else
						{
							sw.WriteLine("Protocol: " + protocol + ", Patient: " + patient + " was not found.");
						}
					}
					catch (Exception ex)
					{
						sw.WriteLine("Protocol: " + protocol + ", Patient: " + patient + " caused an exception.");
						sw.WriteLine("  Error: " + ex.Message);
					}
					finally
					{
						reader.Close();
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
				sw.Close();
			}

		}

		public static DataTable GetDistinctProtocolPatientCensus()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT DISTINCT a.ArpID,c.Protocol,c.Patient FROM aaCombineCensus c " +
				"JOIN ArpActiveProtocols a ON c.Protocol=a.ProtocolID";

			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				SqlCommand cmd = new SqlCommand(sql, conn);
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving distinct protocols and patients");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetActiveInvestigators()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT * FROM ArpActiveInvestigators";

			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				SqlCommand cmd = new SqlCommand(sql, conn);
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving active investigators");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetActiveProtocols()
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT * FROM ArpActiveProtocols";

			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				SqlCommand cmd = new SqlCommand(sql, conn);
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving active protocols");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool AddScatterNurseHoursToCensus(int id, int protocol, int patient, int cat, decimal hours, decimal age, int gender, int ethnicity, int race, DateTime visitDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "INSERT INTO aaCombineCensus (VisitID,Protocol,Patient,Pcategory, " +
				" Hours,Age,Gender,Race,Ethnicity,VisitDate,VisitType) " +
				" Values (@VisitID,@Protocol,@Patient,@Pcategory, " +
				" @Hours,@Age,@Gender,@Race,@Ethnicity,@VisitDate,@VisitType)";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[11];
			aParams[0] = new SqlParameter("@VisitID", SqlDbType.Int, 0, "VisitID");
			aParams[0].Value = id;
			aParams[1] = new SqlParameter("@Protocol", SqlDbType.Int, 0, "Protocol");
			aParams[1].Value = protocol;
			aParams[2] = new SqlParameter("@Patient", SqlDbType.Int, 0, "Patient");
			aParams[2].Value = patient;
			aParams[3] = new SqlParameter("@PCategory", SqlDbType.Int, 0, "PCategory");
			aParams[3].Value = cat;
			aParams[4] = new SqlParameter("@Hours", SqlDbType.Decimal, 2, "Hours");
			aParams[4].Value = hours;
			aParams[5] = new SqlParameter("@Age", SqlDbType.Decimal, 2, "Age");
			aParams[5].Value = age;
			aParams[6] = new SqlParameter("@Gender", SqlDbType.Int, 0, "Gender");
			aParams[6].Value = gender;
			aParams[7] = new SqlParameter("@Race", SqlDbType.Int, 0, "Race");
			aParams[7].Value = race;
			aParams[8] = new SqlParameter("@Ethnicity", SqlDbType.Int, 0, "Ethnicity");
			aParams[8].Value = ethnicity;
			aParams[9] = new SqlParameter("@VisitDate", SqlDbType.DateTime, 0, "VisitDate");
			aParams[9].Value = visitDate;
			aParams[10] = new SqlParameter("@VisitType", SqlDbType.Int, 0, "VisitType");
			aParams[10].Value = 3;

			//SqlConnection conn = DataAccess.GetSqlConnection();
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand cmd = db.GetSqlStringCommand(sql);
			DataAccess.AddParamArrayToDbCommand(cmd, aParams);
			try
			{
				//conn.Open();
				if (db.ExecuteNonQuery(cmd) > 0)
					return true;
				else
					return false;
			}
			catch (Exception ex)
			{
				sb.Append("Error in adding scatter nurse hours census");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static bool SetScatterNurseHoursCensus(DateTime beginDate, DateTime endDate)
		{
			string file = CremaConstants.ReportsDirecotry + "ScatterNurseHoursCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			StringBuilder sql = new StringBuilder("SELECT v.ID,v.Patient,v.Protocol, " +
				" v.VisitDate,v.Duration,v.PCategory, " +
				" p.DOB,p.Gender,p.Ethnicity,p.AmericanIndian,p.Asian, " +
				" p.NativeHawaiian,p.Black,p.White,p.UnknownOr, " +
				" pr.ProtocolNumber " +
				" FROM ScatterNurseHours v " +
				" LEFT JOIN Patient p ON v.Patient=p.ID " +
				" LEFT JOIN Protocol pr ON v.Protocol=pr.ID");

			string criteria = " WHERE VisitDate >= '" + beginDate + "'" +
				" AND VisitDate < '" + endDate + "'";
			sql.Append(criteria);
			sql.Append(" ORDER BY VisitDate");

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			bool exclusion = false;
			int id = 0;
			int patient = 0;
			int protocol = 0;
			string protocolNum = "";
			DateTime visitDate = new DateTime(0);
			int cat = 0;
			int duration = 0;
			DateTime dob = new DateTime(0);
			int gender = 0;
			int ethnicity = 0;
			int race = 0;
			bool bContinue = false;
			bool bPrevRace = false;

			decimal hours = 0;
			decimal age = 0;

			int ord = 0;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					bContinue = true;
					//visit id
					id = reader.GetInt32(0);

					ord = reader.GetOrdinal("Protocol");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Protocol is not entered.");
						bContinue = false;
						exclusion = true;
					}
					else
					{
						protocol = reader.GetInt32(ord);
						ord = reader.GetOrdinal("ProtocolNumber");
						protocolNum = reader.GetString(ord);
					}

					ord = reader.GetOrdinal("Patient");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient is not entered.");
						bContinue = false;
						exclusion = true;
					}
					else
						patient = reader.GetInt32(ord);

					//don't go on if protocol or patient is missing
					if (!bContinue)
						continue;

					ord = reader.GetOrdinal("VisitDate");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Visit date is not entered.");
						exclusion = true;
						continue;
					}
					visitDate = reader.GetDateTime(ord);

					duration = 0;
					ord = reader.GetOrdinal("Duration");
					if (!reader.IsDBNull(ord))
						duration = reader.GetInt32(ord);

					hours = Convert.ToDecimal(Convert.ToDecimal(duration) / 60);

					ord = reader.GetOrdinal("PCategory");
					cat = reader.GetInt32(ord);
					if (cat < 2 || cat > 5)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Category is incorrect.");
						exclusion = true;
						continue;
					}

					ord = reader.GetOrdinal("DOB");
					if (reader.IsDBNull(ord))
					{
						if (patient == 5124)
							dob = visitDate.Subtract(new TimeSpan(183, 0, 0, 0));
						else
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient DOB is not entered for patient id: " + patient);
							exclusion = true;
							continue;
						}
					}
					else
						dob = reader.GetDateTime(ord);

					if (dob.CompareTo(visitDate) > 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient DOB is greater than visit date for " +
							" patient id: " + patient);
						exclusion = true;
						continue;
					}

					//age
					TimeSpan ts = visitDate.Subtract(dob);
					if (ts.Duration().Days == 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Patient DOB is equal to visit date for " +
							" patient id: " + patient);
						exclusion = true;
					}
					if (ts.Duration().Days > 0 && ts.Duration().Days < 11)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Patient DOB is less than 10 days from visit date for " +
							" patient id: " + patient);
						exclusion = true;
					}
					age = Convert.ToDecimal(ts.Duration().Days / 365.25);

					ord = reader.GetOrdinal("Gender");
					gender = reader.GetInt32(ord);

					//ethnicity
					ord = reader.GetOrdinal("Ethnicity");
					ethnicity = reader.GetInt32(ord);

					//race
					race = 7; //initialize to unknownOr

					ord = reader.GetOrdinal("UnknownOr");
					//if it's not unknown
					if (!reader.GetBoolean(ord))
					{
						ord -= 5;  //set ordinal to point to AmericanIndian
						bPrevRace = false;

						//interate thru other 5 race fields
						for (int i = 1; i < 6; i++)
						{
							if (reader.GetBoolean(ord))
							{
								if (bPrevRace)
								{
									race = 6;
									break;
								}
								race = i;
								bPrevRace = true;
							}
							ord++;
						}
					}

					try
					{
						if (exclusion)
							Console.WriteLine("");
						if (!AddScatterNurseHoursToCensus(id, protocol, patient, cat, hours, age, gender, ethnicity, race, visitDate))
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Adding to aaScatterNurseHoursCensus failed.");
						}
					}
					catch (Exception exi)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Exception adding to aaScatterNurseHoursCensus." + exi.Message);
					}
					finally
					{
						sw.Close();
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting scatter nurse hours census census");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				if (conn != null)
					conn.Dispose();
				sw.Close();
			}
		}

		public static bool SetOutpatientCensus(DateTime beginDate, DateTime endDate)
		{
			string file = CremaConstants.ReportsDirecotry + "OutpatientCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			StringBuilder sql = new StringBuilder("SELECT v.ID,v.Patient,v.Protocol, " +
				" v.VisitDate,v.Duration,v.AncilOnly, " +
				" v.PCategory,v.PLocation,v.PSite, " +
				" p.DOB,p.Gender,p.Ethnicity,p.AmericanIndian,p.Asian, " +
				" p.NativeHawaiian,p.Black,p.White,p.UnknownOr, " +
				" pr.ProtocolNumber " +
				" FROM ProtocolVisitOutPatient v " +
				" LEFT JOIN Patient p ON v.Patient=p.ID " +
				" LEFT JOIN Protocol pr ON v.Protocol=pr.ID");

			string criteria = " WHERE VisitDate >= '" + beginDate + "'" +
				" AND VisitDate <= '" + endDate + "'";
			sql.Append(criteria);

			sql.Append(" ORDER BY VisitDate");

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			int id = 0;
			int patient = 0;
			int protocol = 0;
			string protocolNum = "";
			bool ancilOnly = false;
			DateTime visitDate = new DateTime(0);
			int cat = 0;
			int loc = 0;
			int site = 0;
			int duration = 0;
			DateTime dob = new DateTime(0);
			int gender = 0;
			int ethnicity = 0;
			int race = 0;
			bool bContinue = false;
			bool bPrevRace = false;
			bool exclusion = false;

			decimal hours = 0;
			decimal age = 0;

			int ord = 0;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					bContinue = true;
					//visit id
					id = reader.GetInt32(0);

					ord = reader.GetOrdinal("Protocol");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Protocol is not entered.");
						bContinue = false;
						exclusion = true;
					}
					else
					{
						protocol = reader.GetInt32(ord);
						ord = reader.GetOrdinal("ProtocolNumber");
						protocolNum = reader.GetString(ord);
					}

					ord = reader.GetOrdinal("Patient");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient is not entered.");
						bContinue = false;
						exclusion = true;
					}
					else
						patient = reader.GetInt32(ord);

					//don't go on if protocol or patient is missing
					if (!bContinue)
						continue;

					ancilOnly = false;
					ord = reader.GetOrdinal("AncilOnly");
					if (!reader.IsDBNull(ord))
						ancilOnly = reader.GetBoolean(ord);

					ord = reader.GetOrdinal("VisitDate");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Visit date is not entered.");
						exclusion = true;
						continue;
					}
					visitDate = reader.GetDateTime(ord);

					duration = 0;
					ord = reader.GetOrdinal("Duration");
					if (!reader.IsDBNull(ord))
						duration = reader.GetInt32(ord);
					if (duration == 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Duration is not entered.");
						exclusion = true;
					}

					hours = Convert.ToDecimal(Convert.ToDecimal(duration) / 60);

					ord = reader.GetOrdinal("PCategory");
					cat = reader.GetInt32(ord);
					if (cat < 2 || cat > 5)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Category is incorrect.");
						exclusion = true;
						continue;
					}

					ord = reader.GetOrdinal("PLocation");
					loc = reader.GetInt32(ord);
					//**Change this back to < 2
					if (loc < 1 || loc > 4)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - loccation is incorrect.");
						exclusion = true;
						continue;
					}

					ord = reader.GetOrdinal("PSite");
					site = reader.GetInt32(ord);
					if (site < 2 || site > 5)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Site is incorrect.");
						exclusion = true;
						continue;
					}

					ord = reader.GetOrdinal("DOB");
					if (reader.IsDBNull(ord))
					{
						if (patient == 5124)
							dob = visitDate.Subtract(new TimeSpan(183, 0, 0, 0));
						else
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient DOB is not entered for patient id: " + patient);
							exclusion = true;
							continue;
						}
					}
					else
						dob = reader.GetDateTime(ord);

					if (dob.CompareTo(visitDate) > 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient DOB is greater than visit date for " + " patient id: " + patient);
						exclusion = true;
						continue;
					}

					//age
					TimeSpan ts = visitDate.Subtract(dob);
					if (ts.Duration().Days == 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Patient DOB is equal to visit date for " + " patient id: " + patient);
						exclusion = true;
					}
					if (ts.Duration().Days > 0 && ts.Duration().Days < 11)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Patient DOB is less than 10 days from visit date for " +
							" patient id: " + patient);
						exclusion = true;
					}
					age = Convert.ToDecimal(ts.Duration().Days / 365.25);

					ord = reader.GetOrdinal("Gender");
					gender = reader.GetInt32(ord);

					//ethnicity
					ord = reader.GetOrdinal("Ethnicity");
					ethnicity = reader.GetInt32(ord);

					//race
					race = 7; //initialize to unknownOr

					ord = reader.GetOrdinal("UnknownOr");
					//if it's not unknown
					if (!reader.GetBoolean(ord))
					{
						ord -= 5;  //set ordinal to point to AmericanIndian
						bPrevRace = false;

						//interate thru other 5 race fields
						for (int i = 1; i < 6; i++)
						{
							if (reader.GetBoolean(ord))
							{
								if (bPrevRace)
								{
									race = 6;
									break;
								}
								race = i;
								bPrevRace = true;
							}
							ord++;
						}
					}

					try
					{
						if (exclusion)
							Console.WriteLine("");
						if (!AddOutpatientToCensus(id, protocol, patient, cat, loc, site, ancilOnly, hours, age, gender, ethnicity, race, visitDate))
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Adding to aaOutpatientCensus failed.");
						}
					}
					catch (Exception exi)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Exception adding to aaOutpatientCensus." + exi.Message);
					}

				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting outpatient census");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				if (conn != null)
					conn.Dispose();
				sw.Close();
			}
		}

		public static bool SetAdmissionCensus(DateTime beginDate, DateTime endDate)
		{
			string file = CremaConstants.ReportsDirecotry + "AdmissionCensusExclusions";
			StreamWriter sw = new StreamWriter(file, false);
			StringBuilder sb = new StringBuilder();

			StringBuilder sql = new StringBuilder("SELECT v.ID,v.Patient,v.Protocol, " +
				" v.Pending,v.VisitDate,v.DischargeDate,v.TimeIn,v.TimeOut, " +
				" v.PCategory,v.BedType,v.PSite, " +
				" p.DOB,p.Gender,p.Ethnicity,p.AmericanIndian,p.Asian, " +
				" p.NativeHawaiian,p.Black,p.White,p.UnknownOr, " +
				" pr.ProtocolNumber " +
				" FROM ProtocolVisitAdmission v " +
				" LEFT JOIN Patient p ON v.Patient=p.ID " +
				" LEFT JOIN Protocol pr ON v.Protocol=pr.ID");

			string criteria = " WHERE DischargeDate >= '" + beginDate + "'" +
				" AND VisitDate < '" + endDate + "'" +
				" OR DischargeDate IS NULL" +
				" AND VisitDate < '" + endDate + "'";
			sql.Append(criteria);

			sql.Append(" ORDER BY VisitDate");

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			bool exclusion = false;
			int id = 0;
			int patient = 0;
			int protocol = 0;
			string protocolNum = "";
			bool pending = false;
			DateTime admit = new DateTime(0);
			DateTime disch = new DateTime(0);
			string tIn = "";
			string tOut = "";
			int cat = 0;
			int bed = 0;
			int site = 0;
			DateTime dob = new DateTime(0);
			int gender = 0;
			int ethnicity = 0;
			int race = 0;
			bool bContinue = false;
			bool bPrevRace = false;

			int days = 0;
			int scatBDays = 0;
			decimal age = 0;
			decimal excessHrs = 0;

			int ord = 0;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					bContinue = true;
					//visit id
					id = reader.GetInt32(0);

					if (id == 3050)
						Console.WriteLine("");
					ord = reader.GetOrdinal("Protocol");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Protocol is not entered.");
						bContinue = false;
						exclusion = true;
					}
					else
					{
						protocol = reader.GetInt32(ord);
						ord = reader.GetOrdinal("ProtocolNumber");
						protocolNum = reader.GetString(ord);
					}

					ord = reader.GetOrdinal("Patient");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient is not entered.");
						bContinue = false;
						exclusion = true;
					}
					else
						patient = reader.GetInt32(ord);

					//don't go on if protocol or patient is missing
					if (!bContinue)
						continue;

					pending = false;
					ord = reader.GetOrdinal("Pending");
					if (!reader.IsDBNull(ord))
						pending = reader.GetBoolean(ord);

					if (pending)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Admission is pending.");
						exclusion = true;
					}

					ord = reader.GetOrdinal("VisitDate");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Admit date is not entered.");
						exclusion = true;
						continue;
					}
					admit = reader.GetDateTime(ord);

					disch = new DateTime(0);
					ord = reader.GetOrdinal("DischargeDate");
					if (reader.IsDBNull(ord))
					{
						if (!pending)
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Discharge date is not entered and visit is not pending.");
							exclusion = true;
							continue;
						}
						else
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - Warning - Discharge date is not entered, visit is pending.");
							exclusion = true;
						}
					}
					else
						disch = reader.GetDateTime(ord);

					ord = reader.GetOrdinal("TimeIn");
					if (reader.IsDBNull(ord))
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Time-in is null.");
						exclusion = true;
						continue;
					}
					tIn = reader.GetString(ord);
					if (tIn.Trim().Length == 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Time-in is not entered.");
						exclusion = true;
						continue;
					}


					tOut = "";
					ord = reader.GetOrdinal("TimeOut");
					if (reader.IsDBNull(ord))
					{
						if (!pending)
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Time-out is null and visit is not pending.");
							exclusion = true;
							continue;
						}
						else
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - Warning - Time-out is null and visit is pending.");
							exclusion = true;
						}
					}
					else
						tOut = reader.GetString(ord);

					if (tOut.Trim().Length == 0)
					{
						if (!pending)
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Time-out is not entered and visit is not pending.");
							exclusion = true;
							continue;
						}
						else
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - Warning - Time-out is not entered and visit is pending.");
							exclusion = true;
						}
					}

					ord = reader.GetOrdinal("PCategory");
					cat = reader.GetInt32(ord);
					if (cat < 2 || cat > 5)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Category is incorrect.");
						exclusion = true;
						continue;
					}

					ord = reader.GetOrdinal("BedType");
					bed = reader.GetInt32(ord);
					if (bed < 2 || bed > 3) //gcrc or scatter
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Bed type is incorrect.");
						exclusion = true;
						continue;
					}

					//excess hours
					excessHrs = 0;
					if (cat == 3 && bed == 3)
						//Scatter b days are only credited if there was an ancillary on that day
						Console.WriteLine("Do not include Scatter B");
					else
					{
						//only if they are discharged = < endDate
						if (disch.CompareTo(CremaConstants.NullDate) != 0)
						{
							if (disch.CompareTo(endDate) < 1)
								if (tOut.Length > 0)
									excessHrs = GetExcessHours(tIn, tOut);
						}
					}

					ord = reader.GetOrdinal("PSite");
					site = reader.GetInt32(ord);
					if (site < 2 || site > 5)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Site is incorrect.");
						exclusion = true;
						continue;
					}

					//days					
					if (cat == 3 && bed == 3)
					{
						//Scatter b days are only credited if there was an ancillary on that day
						scatBDays = GetScatterBNumberOfDays(id, admit, disch, beginDate, endDate);
						days = scatBDays;
					}
					else
						days = ArpUtils.GetNumberOfDays(admit, disch, beginDate, endDate);

					ord = reader.GetOrdinal("DOB");
					if (reader.IsDBNull(ord))
					{
						if (patient == 5124)
							dob = admit.Subtract(new TimeSpan(183, 0, 0, 0));
						else
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient DOB is not entered for patient id: " + patient);
							exclusion = true;
							continue;
						}
					}
					else
						dob = reader.GetDateTime(ord);

					if (dob.CompareTo(admit) > 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Patient DOB is greater than admit date for " + " patient id: " + patient);
						exclusion = true;
						continue;
					}

					//age
					TimeSpan ts = admit.Subtract(dob);
					if (ts.Duration().Days == 0)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Patient DOB is equal to admit date for " + " patient id: " + patient);
						exclusion = true;
					}
					if (ts.Duration().Days > 0 && ts.Duration().Days < 11)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Patient DOB is less than 10 days from admit date for " + " patient id: " + patient);
						exclusion = true;
					}
					age = Convert.ToDecimal(ts.Duration().Days / 365.25);

					ord = reader.GetOrdinal("Gender");
					gender = reader.GetInt32(ord);

					//ethnicity
					ord = reader.GetOrdinal("Ethnicity");
					ethnicity = reader.GetInt32(ord);
					if (ethnicity == 255)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - WARNING - Ethnicity is not entered for " + " patient id: " + patient);
						exclusion = true;
					}

					//race
					race = 7; //initialize to unknownOr

					ord = reader.GetOrdinal("UnknownOr");
					//if it's not unknown
					if (!reader.GetBoolean(ord))
					{
						ord -= 5;  //set ordinal to point to AmericanIndian
						bPrevRace = false;

						//interate thru other 5 race fields
						for (int i = 1; i < 6; i++)
						{
							if (reader.GetBoolean(ord))
							{
								if (bPrevRace)
								{
									race = 6;
									break;
								}
								race = i;
								bPrevRace = true;
							}
							ord++;
						}
					}

					try
					{
						if (exclusion)
							Console.WriteLine("");
						if (!AddAdmissionToCensus(id, protocol, patient, bed, cat, site, days, age, gender, ethnicity, race, admit, disch, tIn, tOut, excessHrs))
						{
							sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Adding to aaAdmissionCensus failed.");
						}
					}
					catch (Exception exi)
					{
						sw.WriteLine("ID: " + id + ", Protocol: " + protocolNum + " - ERROR - Exception adding to aaAdmissionCensus." + exi.Message);
					}

				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting admission census");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				if (conn != null)
					conn.Dispose();
				sw.Close();
			}
		}

		public static int GetScatterBNumberOfDays(int id, DateTime admit, DateTime disch, DateTime beginDate, DateTime endDate)
		{
			//for scatter b we need to count the actual days that had ancillaries
			StringBuilder sb = new StringBuilder();
			DateTime adjDisch;
			DateTime adjAdmit;

			if (admit.CompareTo(beginDate) < 0)
				adjAdmit = beginDate;
			else
				adjAdmit = admit;

			if (disch.CompareTo(endDate) > 0)
				adjDisch = endDate;
			else
				adjDisch = disch;

			string sql = "SELECT COUNT(DISTINCT TestDate) FROM AncillaryAdmission " +
				" WHERE Visit=" + id +
				" AND TestDate >='" + adjAdmit + "' AND TestDate <='" + adjDisch + "'";

			SqlConnection conn = DataAccess.GetSqlConnection();
			try
			{
				conn.Open();
				return (int)DataAccess.GetScalarObject(CommandType.Text, sql);
			}
			catch (Exception ex)
			{
				sb.Append("Error in GetScatterBNumberOfDays for id:" + id);
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}


		public static decimal GetExcessHours(string tIn, string tOut)
		{

			decimal totMins = 0;
			int hrIn = 0;
			int hrOut = 0;
			if (tIn == "12:25p")
				Console.WriteLine("");
			hrIn = Convert.ToInt32(tIn.Substring(0, 2));
			if (tIn.EndsWith("p"))
			{
				if (hrIn != 12)
					hrIn += 12;
			}
			else
			{
				if (hrIn == 12)
					hrIn = 0;
			}

			hrOut = Convert.ToInt32(tOut.Substring(0, 2));
			if (tOut.EndsWith("p"))
			{
				if (hrOut != 12)
					hrOut += 12;
			}
			else
			{
				if (hrOut == 12)
					hrOut = 0;
			}

			//if this then no extended
			if (hrIn > hrOut)
				return 0;

			totMins = (hrOut - hrIn) * 60;

			//check minutes 									
			int minIn = Convert.ToInt32(tIn.Substring(3, 2));
			int minOut = Convert.ToInt32(tOut.Substring(3, 2));
			totMins += minOut - minIn;

			if (totMins < 0)
				return 0;
			decimal hrMin = new decimal(60.0);
			return Convert.ToDecimal(totMins / hrMin);
		}

		public static bool AddOutpatientToCensus(int id, int protocol, int patient, int cat, int loc, int site,
			bool ancilOnly, decimal hours, decimal age, int gender, int ethnicity, int race, DateTime visitDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "INSERT INTO aaCombineCensus (VisitID,Protocol,Patient,Pcategory,PLocation,PSite, " +
				" AncilOnly,Hours,Age,Gender,Race,Ethnicity,VisitDate,VisitType) " +
				" Values (@VisitID,@Protocol,@Patient,@Pcategory,@PLocation,@PSite, " +
				" @AncilOnly,@Hours,@Age,@Gender,@Race,@Ethnicity,@VisitDate,@VisitType)";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[14];
			aParams[0] = new SqlParameter("@VisitID", SqlDbType.Int, 0, "VisitID");
			aParams[0].Value = id;
			aParams[1] = new SqlParameter("@Protocol", SqlDbType.Int, 0, "Protocol");
			aParams[1].Value = protocol;
			aParams[2] = new SqlParameter("@Patient", SqlDbType.Int, 0, "Patient");
			aParams[2].Value = patient;
			aParams[3] = new SqlParameter("@PCategory", SqlDbType.Int, 0, "PCategory");
			aParams[3].Value = cat;
			aParams[4] = new SqlParameter("@PLocation", SqlDbType.Int, 0, "PLocation");
			aParams[4].Value = loc;
			aParams[5] = new SqlParameter("@PSite", SqlDbType.Int, 0, "PSite");
			aParams[5].Value = site;
			aParams[6] = new SqlParameter("@AncilOnly", SqlDbType.Bit, 0, "AncilOnly");
			aParams[6].Value = ancilOnly;
			aParams[7] = new SqlParameter("@Hours", SqlDbType.Decimal, 2, "Hours");
			aParams[7].Value = hours;
			aParams[8] = new SqlParameter("@Age", SqlDbType.Decimal, 2, "Age");
			aParams[8].Value = age;
			aParams[9] = new SqlParameter("@Gender", SqlDbType.Int, 0, "Gender");
			aParams[9].Value = gender;
			aParams[10] = new SqlParameter("@Race", SqlDbType.Int, 0, "Race");
			aParams[10].Value = race;
			aParams[11] = new SqlParameter("@Ethnicity", SqlDbType.Int, 0, "Ethnicity");
			aParams[11].Value = ethnicity;
			aParams[12] = new SqlParameter("@VisitDate", SqlDbType.DateTime, 0, "VisitDate");
			aParams[12].Value = visitDate;
			aParams[13] = new SqlParameter("@VisitType", SqlDbType.Int, 0, "VisitType");
			aParams[13].Value = 2;

			//SqlConnection conn = DataAccess.GetSqlConnection();
			Database db = DatabaseFactory.CreateDatabase();

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sql);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				if (db.ExecuteNonQuery(cmd) > 0)
					return true;
				else
					return false;
			}
			catch (Exception ex)
			{
				sb.Append("Error in adding outpatient census");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static bool AddAdmissionToCensus(int id, int protocol, int patient, int bed, int cat, int site,
			int days, decimal age, int gender, int ethnicity, int race, DateTime admit, DateTime disch,
			string tIn, string tOut, decimal excessHrs)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "INSERT INTO aaCombineCensus (VisitID,Protocol,Patient,BedType,PCategory,PSite, " +
				" Days,Age,Gender,Race,Ethnicity,VisitDate,DischargeDate,TimeIn,TimeOut,Hours,VisitType) " +
				" Values (@VisitID,@Protocol,@Patient,@BedType,@PCategory,@PSite, " +
				" @Days,@Age,@Gender,@Race,@Ethnicity,@VisitDate,@DischargeDate,@TimeIn,@TimeOut,@Hours,@VisitType)";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[17];
			aParams[0] = new SqlParameter("@VisitID", SqlDbType.Int, 0, "VisitID");
			aParams[0].Value = id;
			aParams[1] = new SqlParameter("@Protocol", SqlDbType.Int, 0, "Protocol");
			aParams[1].Value = protocol;
			aParams[2] = new SqlParameter("@Patient", SqlDbType.Int, 0, "Patient");
			aParams[2].Value = patient;
			aParams[3] = new SqlParameter("@BedType", SqlDbType.Int, 0, "BedType");
			aParams[3].Value = bed;
			aParams[4] = new SqlParameter("@PCategory", SqlDbType.Int, 0, "PCategory");
			aParams[4].Value = cat;
			aParams[5] = new SqlParameter("@PSite", SqlDbType.Int, 0, "PSite");
			aParams[5].Value = site;
			aParams[6] = new SqlParameter("@Days", SqlDbType.Int, 0, "Days");
			aParams[6].Value = days;
			aParams[7] = new SqlParameter("@Age", SqlDbType.Decimal, 2, "Age");
			aParams[7].Value = age;
			aParams[8] = new SqlParameter("@Gender", SqlDbType.Int, 0, "Gender");
			aParams[8].Value = gender;
			aParams[9] = new SqlParameter("@Race", SqlDbType.Int, 0, "Race");
			aParams[9].Value = race;
			aParams[10] = new SqlParameter("@Ethnicity", SqlDbType.Int, 0, "Ethnicity");
			aParams[10].Value = ethnicity;
			aParams[11] = new SqlParameter("@VisitDate", SqlDbType.DateTime, 0, "VisitDate");
			aParams[11].Value = admit;
			aParams[12] = new SqlParameter("@DischargeDate", SqlDbType.DateTime, 0, "DischargeDate");
			aParams[12].Value = disch;
			aParams[13] = new SqlParameter("@TimeIn", SqlDbType.NVarChar, 10, "TimeIn");
			aParams[13].Value = tIn;
			aParams[14] = new SqlParameter("@TimeOut", SqlDbType.NVarChar, 10, "TimeOut");
			aParams[14].Value = tOut;
			aParams[15] = new SqlParameter("@Hours", SqlDbType.Decimal, 2, "Hours");
			aParams[15].Value = excessHrs;
			aParams[16] = new SqlParameter("@VisitType", SqlDbType.Int, 0, "VisitType");
			aParams[16].Value = 1;

			//SqlConnection conn = DataAccess.GetSqlConnection();
			Database db = DatabaseFactory.CreateDatabase();
			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sql);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				if (db.ExecuteNonQuery(cmd) > 0)
					return true;
				else
					return false;
			}
			catch (Exception ex)
			{
				sb.Append("Error in adding admission census");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}

		}

		public static int GetNumberOfDays(DateTime admit, DateTime disch, DateTime beginDate, DateTime endDate)
		{
			DateTime adjDisch;
			DateTime adjAdmit;

			int days = 0;

			//			if(disch.ToShortDateString()=="5/1/2004")
			//				Console.WriteLine("");
			if (admit.CompareTo(beginDate) < 0)
			{
				days = 1;
				adjAdmit = beginDate;
			}
			else
				adjAdmit = admit;

			if (disch.CompareTo(CremaConstants.NullDate) == 0)
				adjDisch = endDate;
			else
			{
				if (disch.CompareTo(endDate) > 0)
					adjDisch = endDate;
				else
					adjDisch = disch;
			}

			TimeSpan ts = adjDisch.Subtract(adjAdmit);
			days += ts.Days;

			return days;
		}

		public static int GetNumberOfMonths(DateTime fromDate, DateTime toDate)
		{
			int y1 = fromDate.Year;
			int y2 = toDate.Year;

			int yDiff = y2 - y1;

			int m1 = fromDate.Month;
			int m2 = toDate.Month;

			return ((12 * yDiff) + (m2 - m1) + 1);
		}

		public static bool AddAncillaryToArp(DataRow dr)
		{
			OleDbConnection conn = null;
			try
			{
				conn = new OleDbConnection(_arpConnStr);
				conn.Open();

				string sql = "INSERT INTO ANCILLARIES (Costcent,Gross_ai1,Gross_bi1,Adjusti1, " +
					" Gross_ai2,Gross_bi2,Adjusti2,Gross_ao1,Gross_bo1,Adjusto1, " +
					" Gross_ao2,Gross_bo2,Adjusto2)" +
					" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) ";

				OleDbCommand cmd = new OleDbCommand(sql, conn);

				OleDbParameter param = new OleDbParameter("Costcent", OleDbType.VarChar, 25, "Costcent");
				param.Value = dr["CostCenter"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_ai1", OleDbType.Numeric, 0, "Gross_ai1");
				if (dr["GrossAI1"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossAI1"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_bi1", OleDbType.Numeric, 0, "Gross_bi1");
				if (dr["GrossBI1"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossBI1"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Adjusti1", OleDbType.Numeric, 0, "Adjusti1");
				if (dr["AdjustI1"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["AdjustI1"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_ai2", OleDbType.Numeric, 0, "Gross_ai2");
				if (dr["GrossAI2"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossAI2"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_bi2", OleDbType.Numeric, 0, "Gross_bi2");
				if (dr["GrossBI2"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossBI2"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Adjusti2", OleDbType.Numeric, 0, "Adjusti2");
				if (dr["AdjustI2"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["AdjustI2"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_ao1", OleDbType.Numeric, 0, "Gross_ao1");
				if (dr["GrossAO1"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossAO1"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_bo1", OleDbType.Numeric, 0, "Gross_bo1");
				if (dr["GrossBO1"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossBO1"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Adjusto1", OleDbType.Numeric, 0, "Adjusto1");
				if (dr["AdjustO1"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["AdjustO1"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_ao2", OleDbType.Numeric, 0, "Gross_ao2");
				if (dr["GrossAO2"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossAO2"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Gross_bo2", OleDbType.Numeric, 0, "Gross_bo2");
				if (dr["GrossBO2"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["GrossBO2"];
				cmd.Parameters.Add(param);

				param = new OleDbParameter("Adjusto2", OleDbType.Numeric, 0, "Adjusto2");
				if (dr["AdjustO2"] == System.DBNull.Value)
					param.Value = 0;
				else
					param.Value = dr["AdjustO2"];
				cmd.Parameters.Add(param);

				cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}

		}

		public static bool AddToArpAncillaries(DataTable dt)
		{
			StringBuilder sb = new StringBuilder();
			try
			{
				foreach (DataRow dr in dt.Rows)
				{
					AddAncillaryToArp(dr);
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in adding arp ancillaries");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}

		}

		public static ArrayList SetOrderForRateChangeDates(Hashtable ht, DateTime rptBeginDate, DateTime rptEndDate)
		{
			ArrayList alTemp = new ArrayList();
			ArrayList alRet = new ArrayList();
			DateTime[] dtSet;
			DateTime dtStart;
			DateTime dtNext;
			DateTime dtEnd;

			//transfer from hash to arrayList
			foreach (object obj in ht.Values)
			{
				dtSet = (DateTime[])obj;
				alTemp.Add(dtSet[0]);
			}
			//sort the arrayList
			alTemp.Sort();

			for (int i = 0; i < alTemp.Count; i++)
			{
				dtStart = (DateTime)alTemp[i];

				//if last one then use report end date
				if (i == alTemp.Count - 1)
				{
					alRet.Add(new DateTime[2] { dtStart, rptEndDate });
					break;
				}

				dtNext = (DateTime)alTemp[i + 1];
				//skip this one and calculate on the next one if next start date = this start date
				if (dtNext == dtStart)
					continue;

				dtEnd = dtNext.AddDays(1);
				alRet.Add(new DateTime[2] { dtStart, dtEnd });
			}
			return alRet;
		}

		public static bool GetAncillaryAdmissionCensusGrouped(DataTable dt, int numChange, int category, DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT c.Display AS CostCenter, " +
				" SUM(Charge) AS Gross,Rate,p.Display AS Category " +
				" FROM aaAncillaryCensusIn i, CostCenter c, PCategory_L p " +
				" WHERE i.CostCenter=c.ID AND i.PCategory=" + category +
				" AND i.PCategory=p.ID " +
				" AND TestDate >='" + beginDate.ToShortDateString() + "' " +
				" AND TestDate <='" + endDate.ToShortDateString() + "' " +
				" GROUP BY c.Display,p.Display,Rate " +
				" ORDER BY c.Display,p.Display";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			string costCenter = "";
			decimal gross = 0;
			decimal rate = 0;

			DataRow dr = null;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					costCenter = reader.GetString(0);
					gross = reader.GetDecimal(1);
					rate = reader.GetDecimal(2);

					dr = dt.Rows.Find(costCenter);
					if (numChange == 1)
					{
						if (category == 2)
						{
							dr["GrossAI1"] = gross;
							dr["AdjustI1"] = rate;
						}
						else
						{
							dr["GrossBI1"] = gross;
							dr["AdjustI1"] = rate;
						}
					}
					else
					{
						if (category == 2)
						{
							dr["GrossAI2"] = gross;
							dr["AdjustI2"] = rate;
						}
						else
						{
							dr["GrossBI2"] = gross;
							dr["AdjustI2"] = rate;
						}
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in getting grouped ancillary admissions census ");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static ArrayList GetAncillaryRateChangeDates(DateTime rptBeginDate, DateTime rptEndDate)
		{
			StringBuilder sb = new StringBuilder();
			ArrayList al = new ArrayList();
			string sql = "SELECT DISTINCT AncillaryRate.StartDate " +
				"FROM AncillaryRate " +
				"WHERE (AncillaryRate.StartDate < '" + rptEndDate.ToShortDateString() + "') " +
				"AND (AncillaryRate.EndDate > '" + rptBeginDate.ToShortDateString() + "') " +
				"AND AncillaryRate.CostCenter IN " +
				"(SELECT DISTINCT c.ID " +
				"FROM AncillaryAdmission a, ProtocolVisitAdmission p, CostCenter c " +
				"WHERE a.Visit=p.ID AND (p.PCategory=2 OR p.PCategory=3) " +
				"AND (p.BedType=2 OR p.BedType=3) " +
				"AND TestDate>='" + rptBeginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + rptEndDate.ToShortDateString() + "' " +
				"AND a.CostCenter=c.ID " +
				"UNION " +
				"SELECT DISTINCT c.ID " +
				"FROM AncillaryOutPatient a, ProtocolVisitOutPatient p, CostCenter c " +
				"WHERE a.Visit=p.ID AND (p.PCategory=2 OR p.PCategory=3) " +
				"AND TestDate>='" + rptBeginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + rptEndDate.ToShortDateString() + "' " +
				"AND a.CostCenter=c.ID) ORDER BY AncillaryRate.StartDate";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			DateTime dt;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					//dt = new DateTime();
					dt = reader.GetDateTime(0);
					if (dt.CompareTo(rptBeginDate) < 1)
						continue;
					if (dt.CompareTo(rptEndDate) > -1)
						continue;
					al.Add(dt);
				}
				return al;
			}
			catch (Exception ex)
			{
				sb.Append("Error in GetAncillaryRateChangeDates");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static DataTable GetAncillaryRateChangesForDateRange(DataTable dtCostCenters, DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			//dtActiveCostCenters has two columns: Display(cost center name) and CostCenter(id)
			DataTable dt = dtCostCenters;  //reference shorthand
			if (dt == null)
				dt = GetCostCentersChargedForDates(beginDate, endDate);

			//initialize rate change table
			DataTable dtRateChange = new DataTable("Report");
			DataColumn dc = new DataColumn("CostCenterID", typeof(System.Int32));
			dc.Caption = "ID";
			dc.ExtendedProperties.Add("Width", 20);
			dtRateChange.Columns.Add(dc);

			dc = new DataColumn("CostCenterName", typeof(System.String));
			dc.Caption = "Name";
			dc.ExtendedProperties.Add("Width", 180);
			dtRateChange.Columns.Add(dc);

			dc = new DataColumn("StartDate", typeof(System.DateTime));
			dc.Caption = "Start";
			dc.ExtendedProperties.Add("Width", 60);
			dtRateChange.Columns.Add(dc);

			dc = new DataColumn("EndDate", typeof(System.DateTime));
			dc.Caption = "End";
			dc.ExtendedProperties.Add("Width", 60);
			dtRateChange.Columns.Add(dc);

			dc = new DataColumn("Rate", typeof(System.Decimal));
			dc.Caption = "Rate";
			dc.ExtendedProperties.Add("Width", 60);
			dtRateChange.Columns.Add(dc);

			string sql = "SELECT * FROM AncillaryRate WHERE CostCenter=";
			string sqlComp = "";
			//SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			try
			{
				DateTime dtStart;
				DateTime dtEnd;
				int ord;
				//conn.Open();
				DataRow drChange;

				foreach (DataRow dr in dt.Rows)
				{
					int ccID = (int)dr["CostCenter"];
					sqlComp = sql + ccID.ToString() + " ORDER BY EndDate DESC";

					reader = DataAccess.GetDataReader(sqlComp);
					while (reader.Read())
					{
						ord = reader.GetOrdinal("StartDate");
						dtStart = reader.GetDateTime(ord);
						ord = reader.GetOrdinal("EndDate");
						dtEnd = reader.GetDateTime(ord);

						//check to see if it applies to the report dates
						if (dtEnd > beginDate && dtStart < endDate)
						{
							ord = reader.GetOrdinal("Rate");

							drChange = dtRateChange.NewRow();
							dtRateChange.Rows.Add(drChange);
							drChange["CostCenterID"] = ccID;
							drChange["CostCenterName"] = dr["Display"].ToString();
							drChange["StartDate"] = dtStart;
							drChange["EndDate"] = dtEnd;
							drChange["Rate"] = reader.GetDecimal(ord);
						}
					}
					reader.Close();
				}
				return dtRateChange;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute 'GetRateChangesForCostCenters'.");
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private static DataTable InitializeDataTable(ArrayList columns)
		{
			DataTable dt = new DataTable("Report");
			foreach (ReportColumnInfo rci in columns)
			{
				DataColumn dc = new DataColumn(rci.FieldName, Type.GetType(rci.FieldType));
				dc.Caption = rci.DisplayName;
				dc.ExtendedProperties.Add("Width", rci.ColumnWidth);
				dt.Columns.Add(dc);
			}
			return dt;
		}

		public static DataTable GetPatientMrnDuplicates(FormReportInfo _reportMeta)
		{
			StringBuilder sb = new StringBuilder();
			string spName = "PatientMrnDuplicates";
			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			DataTable dt = InitializeDataTable(_reportMeta.ReportColumns);
			int dupes;
			try
			{
				//conn.Open();
				reader = DataAccess.GetDataReader(CommandType.StoredProcedure, spName);
				while (reader.Read())
				{
					DataRow dr = dt.NewRow();
					dupes = reader.GetInt32(1);
					if (dupes == 1)
						break;
					dr[1] = dupes;
					dr[0] = reader.GetString(0);
					dt.Rows.Add(dr);
				}
				reader.Close();
				return dt;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute 'GetPatientMrnDuplicates'.");
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				throw new Exception(sb.ToString());
			}
		}

		public static DataTable GetAncillaryDuplicates(FormReportInfo reportMeta)
		{
			StringBuilder sb = new StringBuilder();
			string spName = reportMeta.SPName;
			//SqlConnection conn = DataAccess.GetSqlConnection();	
			IDataReader reader = null;

			DataTable dt = InitializeDataTable(reportMeta.ReportColumns);
			int dupes;
			try
			{
				//conn.Open();
				reader = DataAccess.GetDataReader(CommandType.StoredProcedure, spName);
				while (reader.Read())
				{
					DataRow dr = dt.NewRow();
					dupes = reader.GetInt32(6);
					if (dupes == 1)
						break;
					dr[6] = dupes;
					dr[0] = reader.GetInt32(0);
					dr[1] = reader.GetInt32(1);
					dr[2] = reader.GetDateTime(2);
					dr[3] = reader.GetInt32(3);
					dr[4] = reader.GetDecimal(4);
					if (reader.IsDBNull(5))
						dr[5] = "";
					else
						dr[5] = reader.GetString(5);

					dt.Rows.Add(dr);
				}
				return dt;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute 'GetAncillaryDuplicates'.");
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				throw new Exception(sb.ToString());
			}
		}

		public static DataTable GetVisitDuplicates(FormReportInfo reportMeta)
		{
			StringBuilder sb = new StringBuilder();
			string spName = reportMeta.SPName;
			//SqlConnection conn = DataAccess.GetSqlConnection();	
			IDataReader reader = null;

			DataTable dt = InitializeDataTable(reportMeta.ReportColumns);
			int dupes;
			try
			{
				//conn.Open();
				reader = DataAccess.GetDataReader(CommandType.StoredProcedure, spName);
				while (reader.Read())
				{
					DataRow dr = dt.NewRow();
					dupes = reader.GetInt32(4);
					if (dupes == 1)
						break;
					dr[4] = dupes;
					dr[0] = reader.GetInt32(0);
					dr[1] = reader.GetInt32(1);
					dr[2] = reader.GetInt32(2);
					dr[3] = reader.GetDateTime(3);
					dt.Rows.Add(dr);
				}
				reader.Close();
				return dt;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute 'GetVisitDuplicates'.");
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				throw new Exception(sb.ToString());
			}
		}

		//public static DataTable GetReportDataForDates(FormReportParamInfo reportParamInfo)
		//{
		//    //returns distinct cost centers (name and id) charged 
		//    //for ancillaries (both admission and outpatient ancillaries)
		//    //for dates given
		//    object[] paramsObj = new object[2];
		//    paramsObj[0] = reportParamInfo.BeginDate;
		//    paramsObj[1] = reportParamInfo.EndDate;
		//    SqlParameter[] aparams = new SqlParameter[2];

		//    StringBuilder sb = new StringBuilder();			
		//    //SqlConnection conn = DataAccess.GetSqlConnection();						
		//    try
		//    {
		//        //conn.Open();
		//        DataSet ds =  SqlHelper.ExecuteDataset(conn, reportParamInfo.SPName, paramsObj);
		//        if(ds.Tables.Count >0)		
		//        {
		//            DataTable dt = ds.Tables[0];
		//            dt.TableName = "Report";
		//            return dt;
		//        }								
		//        else
		//            return null;
		//    }
		//    catch(Exception ex)
		//    {
		//        sb.Append("Could not execute 'GetActiveProtocolsForDates'.");
		//        sb.Append(Environment.NewLine);
		//        sb.Append("Error: " + ex.Message);
		//        throw new Exception(sb.ToString());				
		//    }
		//    finally
		//    {
		//        if(conn != null)
		//            conn.Dispose();
		//    }
		//}

		public static DataTable GetActiveProtocolsForDates(DateTime beginDate, DateTime endDate)
		{
			//returns distinct cost centers (name and id) charged 
			//for ancillaries (both admission and outpatient ancillaries)
			//for dates given
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT DISTINCT v.Protocol AS ProtocolID,p.ProtocolNumber,p.SPID " +
				" FROM ProtocolVisitAdmission v,Protocol p" +
				" WHERE DischargeDate>='" + beginDate.ToShortDateString() +
				"' AND VisitDate<'" + endDate.ToShortDateString() + "' " +
				" AND v.Protocol=p.ID " +
				" UNION " +
				" SELECT DISTINCT v.Protocol AS ProtocolID,p.ProtocolNumber,p.SPID " +
				" FROM ProtocolVisitOutPatient v,Protocol p" +
				" WHERE VisitDate>='" + beginDate.ToShortDateString() +
				"' AND VisitDate<='" + endDate.ToShortDateString() + "' " +
				" AND v.Protocol=p.ID " +
				" UNION " +
				" SELECT DISTINCT v.Protocol AS ProtocolID,p.ProtocolNumber,p.SPID " +
				" FROM ScatterNurseHours v,Protocol p" +
				" WHERE VisitDate>='" + beginDate.ToShortDateString() +
				"' AND VisitDate<='" + endDate.ToShortDateString() + "' " +
				" AND v.Protocol=p.ID";

			//SqlConnection conn = DataAccess.GetSqlConnection();			
			try
			{
				//conn.Open();
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
				{
					DataTable dt = ds.Tables[0];
					dt.TableName = "Report";
					//format columns for report
					DataColumn dc = dt.Columns["ProtocolID"];
					dc.Caption = "Protocol ID";
					dc.ExtendedProperties.Add("Width", 75);

					dc = dt.Columns["ProtocolNumber"];
					dc.Caption = "Protocol Number";
					dc.ExtendedProperties.Add("Width", 100);

					dc = dt.Columns["SPID"];
					dc.Caption = "SPID";
					dc.ExtendedProperties.Add("Width", 50);

					return dt;
				}
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute 'GetActiveProtocolsForDates'.");
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		public static DataTable GetCostCentersChargedForDates(DateTime beginDate, DateTime endDate)
		{
			//returns distinct cost centers (name and id) charged 
			//for ancillaries (both admission and outpatient ancillaries)
			//for dates given
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT DISTINCT c.Display, a.CostCenter " +
				" FROM AncillaryAdmission a, ProtocolVisitAdmission p, CostCenter c " +
				" WHERE a.Visit=p.ID AND (p.PCategory=2 OR p.PCategory=3) " +
				" AND (p.BedType=2 OR p.BedType=3) " +
				" AND TestDate>='" + beginDate + "' AND TestDate<='" + endDate + "' " +
				" AND a.CostCenter=c.ID " +
				" UNION " +
				" SELECT DISTINCT c.Display, a.CostCenter " +
				" FROM AncillaryOutPatient a, ProtocolVisitOutPatient p, CostCenter c " +
				" WHERE a.Visit=p.ID AND (p.PCategory=2 OR p.PCategory=3) " +
				" AND TestDate>='" + beginDate + "' AND TestDate<='" + endDate + "' " +
				" AND a.CostCenter=c.ID " +
				" ORDER BY c.Display";

			//SqlConnection conn = DataAccess.GetSqlConnection();			
			try
			{
				//conn.Open();
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
				{
					DataTable dt = ds.Tables[0];
					dt.TableName = "Report";
					//format columns for report
					DataColumn dc = dt.Columns["Display"];
					dc.Caption = "Name";
					dc.ExtendedProperties.Add("Width", 150);

					dc = dt.Columns["CostCenter"];
					dc.Caption = "ID";
					dc.ExtendedProperties.Add("Width", 30);

					return dt;
				}
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute 'GetCostCenters'.");
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		public static DataTable GetAdmissionExcessHoursDaysPatientCare()
		{
			//time set is already considered in aaCombineCensus
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT Count(*)AS Days,PCategory FROM aaCombineCensus " +
				"WHERE VisitType=1 AND BedType=2 AND Hours>0 " +
				"GROUP BY PCategory";

			SqlConnection conn = DataAccess.GetSqlConnection();

			try
			{
				conn.Open();
				DataSet ds = DataAccess.GetDataSet(sql);
				if (ds.Tables.Count > 0)
					return ds.Tables[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				sb.Append("Error in GetAdmissionExcessHoursDaysPatientCare");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetAdmissionDaysPatientCare(int rateChange)
		{
			//time period is already considered when constructing aaCombineCensus
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT SUM(Days) AS Days,PCategory,BedType " +
				"FROM aaCombineCensus WHERE VisitType=1 " +
				"GROUP BY PCategory,BedType";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			int days;
			int category;
			int bedType;
			int ancAdays = 0;
			int ancBdays = 0;
			int crCdays = 0;
			int crDdays = 0;
			int sbAdays = 0;
			int sbBdays = 0;

			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					days = reader.GetInt32(0);
					category = reader.GetInt32(1);
					bedType = reader.GetInt32(2);
					switch (category)
					{
						case 2:
							if (bedType == 2)
								ancAdays = days;
							else
								sbAdays = days;
							break;
						case 3:
							if (bedType == 2)
								ancBdays = days;
							else
								sbBdays = days;
							break;
						case 4:
							if (bedType == 2)
								crCdays = days;
							break;
						case 5:
							if (bedType == 2)
								crDdays = days;
							break;
					}
				}


				ArpUtils.UpdatePCCARAdmissionDays(rateChange, ancAdays, ancBdays, sbAdays, sbBdays, crCdays, crDdays);

				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in SetAdmissionDaysPatientCare");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetOutpatientDaysPatientCare(int rateChange)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT Count(*)AS Days,PCategory " +
				"FROM aaCombineCensus WHERE VisitType=2 " +
				"GROUP BY PCategory";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			int days;
			int category;
			int ancAvisit = 0;
			int ancBvisit = 0;
			int ocrCdays = 0;
			int ocrDdays = 0;

			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					days = reader.GetInt32(0);
					category = reader.GetInt32(1);
					switch (category)
					{
						case 2:
							ancAvisit = days;
							break;
						case 3:
							ancBvisit = days;
							break;
						case 4:
							ocrCdays = days;
							break;
						case 5:
							ocrDdays = days;
							break;
					}
				}
				ArpUtils.UpdatePCCAROutpatientDays(rateChange, ancAvisit, ancBvisit, ocrCdays, ocrDdays);
				DataTable dt = ArpUtils.GetAdmissionExcessHoursDaysPatientCare();
				if (dt != null)
				{
					foreach (DataRow dr in dt.Rows)
					{
						days = (int)dr["Days"];
						category = (int)dr["PCategory"];
						switch (category)
						{
							case 2:
								ancAvisit += days;
								break;
							case 3:
								ancBvisit += days;
								break;
							case 4:
								ocrCdays += days;
								break;
							case 5:
								ocrDdays += days;
								break;
						}

					}
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in SetAdmissionDaysPatientCare");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetAncillaryAdmissionPatientCare(int rateChange, DateTime rptBeginDate, DateTime rptEndDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT SUM(Charge*Rate/100) AS TotalNet,PCategory,BedType " +
				"FROM aaAncillaryCensusIN " +
				"WHERE TestDate>='" + rptBeginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + rptEndDate.ToShortDateString() + "' " +
				"GROUP BY PCategory,BedType";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			decimal charge;
			int category;
			int bedType;
			decimal ancAcost = 0;
			decimal ancBcost = 0;
			decimal ancSBAcost = 0;
			decimal ancSBBcost = 0;

			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					charge = reader.GetDecimal(0);
					category = reader.GetInt32(1);
					bedType = reader.GetInt32(2);
					switch (category)
					{
						case 2:
							if (bedType == 2)
								ancAcost = charge;
							else
								ancSBAcost = charge;
							break;
						case 3:
							if (bedType == 2)
								ancBcost = charge;
							else
								ancSBBcost = charge;
							break;
					}
				}
				ArpUtils.UpdatePCCARAncillaryAdmission(rateChange, ancAcost, ancBcost, ancSBAcost, ancSBBcost);

				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in SetAncillaryAdmissionPatientCare");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetAncillaryOutpatientPatientCare(int rateChange, DateTime rptBeginDate, DateTime rptEndDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT SUM(Charge*Rate/100) AS TotalNet, PCategory " +
				"FROM aaAncillaryCensusOut " +
				"WHERE TestDate>='" + rptBeginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + rptEndDate.ToShortDateString() + "' " +
				"GROUP BY PCategory";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			decimal charge;
			int category;
			decimal ancAOcost = 0;
			decimal ancBOcost = 0;

			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					charge = reader.GetDecimal(0);
					category = reader.GetInt32(1);

					if (category == 2)
						ancAOcost = charge;
					else
						ancBOcost = charge;
				}
				ArpUtils.UpdatePCCARAncillaryOutpatient(rateChange, ancAOcost, ancBOcost);

				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in SetAncillaryOutpatientPatientCare");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}


		public static ArrayList GetMissingAncillaryRates(DateTime beginDate, DateTime endDate)
		{
			//This query selects distinct cost centers used during the report period
			//from the ancillary tables.  It then selects the cost centers that have  
			//ancillary rates that fall within the report period.  If the first select has
			//cost centers not in the second select then there are missing rates.
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT DISTINCT a.CostCenter FROM AncillaryOutPatient a " +
				"WHERE TestDate>='" + beginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + endDate.ToShortDateString() + "' " +
				"AND a.CostCenter NOT IN (SELECT a.CostCenter " +
				"FROM AncillaryOutPatient a " +
				"JOIN ProtocolVisitOutPatient p ON a.Visit=p.ID " +
				"JOIN AncillaryRate r ON a.CostCenter=r.CostCenter " +
				"WHERE (p.PCategory=2 OR p.PCategory=3) " +
				"AND TestDate>='" + beginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + endDate.ToShortDateString() + "' " +
				"AND r.StartDate<=a.TestDate AND r.EndDate>=a.TestDate) " +
				"UNION SELECT DISTINCT a.CostCenter " +
				"FROM AncillaryAdmission a " +
				"WHERE TestDate>='" + beginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + endDate.ToShortDateString() + "' " +
				"AND a.CostCenter NOT IN (SELECT a.CostCenter " +
				"FROM AncillaryAdmission a " +
				"JOIN ProtocolVisitAdmission p ON a.Visit=p.ID " +
				"JOIN AncillaryRate r ON a.CostCenter=r.CostCenter " +
				"WHERE (p.PCategory=2 OR p.PCategory=3) " +
				"AND (p.BedType=2 OR p.BedType=3) " +
				"AND TestDate>='" + beginDate.ToShortDateString() + "' " +
				"AND TestDate<='" + endDate.ToShortDateString() + "' " +
				"AND r.StartDate<=a.TestDate AND r.EndDate>=a.TestDate)";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			ArrayList al = new ArrayList();
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					al.Add(reader.GetInt32(0));
				}
				return al;
			}
			catch (Exception ex)
			{
				sb.Append("Error in GetMissingAncillaryRates");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetAncillaryCensusIn(DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "Insert INTO aaAncillaryCensusIn " +
				" SELECT a.ID,a.TestDate,a.CostCenter,a.Charge,p.PCategory,p.BedType,r.Rate*100 " +
				" FROM AncillaryAdmission a " +
				" JOIN ProtocolVisitAdmission p ON a.Visit=p.ID " +
				" JOIN AncillaryRate r ON a.CostCenter=r.CostCenter " +
				" WHERE (p.PCategory=2 OR p.PCategory=3) " +
				" AND (p.BedType=2 OR p.BedType=3) " +
				" AND TestDate>='" + beginDate.ToShortDateString() + "'" +
				" AND TestDate<='" + endDate.ToShortDateString() + "' " +
				" AND r.StartDate<=a.TestDate AND r.EndDate>=a.TestDate " +
				" ORDER BY a.CostCenter ";

			//SqlConnection conn = DataAccess.GetSqlConnection();	

			try
			{
				//conn.Open();
				DataAccess.ExecuteNonQuery(CommandType.Text, sql);
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting Ancillary Census In");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static bool GetAncillaryOutCensusGrouped(DataTable dt, int numChange, int category, DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT c.Display AS CostCenter, " +
				" SUM(Charge) AS GROSS,RATE,p.Display AS Category " +
				" FROM aaAncillaryCensusOut i, CostCenter c, PCategory_L p " +
				" WHERE i.CostCenter=c.ID AND i.PCategory=" + category +
				" AND i.PCategory=p.ID " +
				" AND TestDate >='" + beginDate.ToShortDateString() + "' " +
				" AND TestDate <='" + endDate.ToShortDateString() + "' " +
				" GROUP BY c.Display,p.Display,Rate " +
				" ORDER BY c.Display,p.Display";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			string costCenter = "";
			decimal gross = 0;
			decimal rate = 0;

			DataRow dr = null;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					costCenter = reader.GetString(0);
					gross = reader.GetDecimal(1);
					rate = reader.GetDecimal(2);

					dr = dt.Rows.Find(costCenter);
					if (numChange == 1)
					{
						if (category == 2)
						{
							dr["GrossAO1"] = gross;
							dr["AdjustO1"] = rate;
						}
						else
						{
							dr["GrossBO1"] = gross;
							dr["AdjustO1"] = rate;
						}
					}
					else
					{
						if (category == 2)
						{
							dr["GrossAO2"] = gross;
							dr["AdjustO2"] = rate;
						}
						else
						{
							dr["GrossBO2"] = gross;
							dr["AdjustO2"] = rate;
						}
					}

				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in getting grouped ancillary outpatient census ");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}
		}

		public static bool SetAncillaryCensusOut(DateTime beginDate, DateTime endDate)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "Insert INTO aaAncillaryCensusOut " +
				" SELECT a.ID, a.TestDate,a.CostCenter,a.Charge,p.PCategory,r.Rate*100 " +
				" FROM AncillaryOutPatient a " +
				" JOIN ProtocolVisitOutPatient p ON a.Visit=p.ID " +
				" JOIN AncillaryRate r ON a.CostCenter=r.CostCenter " +
				" WHERE (p.PCategory=2 OR p.PCategory=3) " +
				" AND TestDate>='" + beginDate.ToShortDateString() + "'" +
				" AND TestDate<='" + endDate.ToShortDateString() + "' " +
				" AND r.StartDate<=a.TestDate AND r.EndDate>=a.TestDate " +
				" ORDER BY a.CostCenter ";

			//SqlConnection conn = DataAccess.GetSqlConnection();
			//SqlCommand cmd = new SqlCommand(sql, conn);

			try
			{
				//conn.Open();
				DataAccess.ExecuteNonQuery(CommandType.Text, sql);
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting Ancillary Census Out");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				//if(conn != null)
				//conn.Dispose();				
			}
		}

		public static bool SetDistinctAncillaryCostCenter(DateTime beginDate, DateTime endDate, DataTable dt)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT DISTINCT c.Display " +
				" FROM AncillaryAdmission a " +
				" JOIN ProtocolVisitAdmission p ON a.Visit=p.ID " +
				" JOIN CostCenter c ON a.CostCenter=c.ID " +
				" WHERE (p.PCategory=2 OR p.PCategory=3) " +
				" AND (p.BedType=2 OR p.BedType=3) " +
				" AND TestDate>='" + beginDate + "' AND TestDate<='" + endDate + "' " +
				" UNION SELECT DISTINCT c.Display " +
				" FROM AncillaryOutPatient a " +
				" JOIN ProtocolVisitOutPatient p ON a.Visit=p.ID " +
				" JOIN CostCenter c ON a.CostCenter=c.ID " +
				" WHERE (p.PCategory=2 OR p.PCategory=3) " +
				" AND TestDate>='" + beginDate + "' AND TestDate<='" + endDate + "' " +
				" ORDER BY c.Display";

			SqlConnection conn = DataAccess.GetSqlConnection();
			IDataReader reader = null;

			DataRow dr = null;
			try
			{
				conn.Open();
				reader = DataAccess.GetDataReader(sql);
				while (reader.Read())
				{
					dr = dt.NewRow();
					dr["CostCenter"] = reader.GetString(0);
					dt.Rows.Add(dr);
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Error in setting active investigators");
				sb.Append(Environment.NewLine);
				sb.Append(ex.Message);
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (conn != null)
					conn.Dispose();
			}

		}
	}

	public class DateSet
	{
		public DateTime Start;
		public DateTime End;
	}

	public class CremaAprIDs
	{
		public int ID;
		public int ArpID;
	}

	public class CenPSCounter
	{
		public Hashtable DaysOpen = new Hashtable();
		public int ADays = 0;
		public int BDays = 0;
		public int CDays = 0;
		public int DDays = 0;
		public int SADays = 0;
		public int SBDays = 0;
		public int SDDays = 0;
		public int AVisits = 0;
		public int BVisits = 0;
		public int CVisits = 0;
		public int DVisits = 0;
		public int AOrvs = 0;
		public int BOrvs = 0;
		public CenPSCounter()
		{
		}
	}
}
